Click here to Skip to main content
16,016,022 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

 
Share this answer
 
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[^]
 
Share this answer
 

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