Click here to Skip to main content
14,981,532 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
This is my current result set of my query:

Question   Sol25A   Sol25B   Sol25C   Sol40A   Sol40B
======================================================
A           1        4          2       6         0
B           2        3          2       1         9
C           6        7          1       0         8
======================================================
Total =    9         14         5       7         17
======================================================

And I want the result in this form:
Product   Total
===============
Sol25A      9
Sol25B      14
Sol25C      5
Sol40A      7
Sol40B      17


Can you please guide me that how I should do the query for this.

What I have tried:

I have tried the Poivot process
Posted
Updated 18-May-21 10:10am
v2

   
Well, you need to 1) unpivot data then to 2) sum them.

See:
SQL
CREATE TABLE Questions
(
  Question varchar(30),
  Sol25A int,
  Sol25B int,
  Sol25C int,
  Sol40A int,
  Sol40B int
);

INSERT INTO Questions (Question, Sol25A, Sol25B, Sol25C, Sol40A, Sol40B)
VALUES('A', 1, 4, 2, 6, 0),
('B', 2, 3, 2, 1, 9),
('C', 6, 7, 1, 0, 8);

SELECT Solution, SUM(Points) Total
FROM   
  (
    SELECT Question, Sol25A, Sol25B, Sol25C, Sol40A, Sol40B  
    FROM Questions
    ) pvt  
UNPIVOT (Points FOR Solution IN (Sol25A, Sol25B, Sol25C, Sol40A, Sol40B)  
)
AS unpvt
GROUP BY Solution;


Result:
Solution 	Total
Sol25A 	9
Sol25B 	14
Sol25C 	5
Sol40A 	7
Sol40B 	17


Link to fiddle: SQL Server 2019 | db<>fiddle[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900