Convert column into the rows

Question

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 provide me the query for me, this will be the great help for me.

Better build query yourself and if it doesn't work, show us and let it fix then. SO is not code writing service. — Arvo, May 18 '21 at 07:10

score 0 · Accepted Answer · answered May 18 '21 at 11:15

I would suggest that you unpivot using cross apply and then aggregate:

select product, sum(val)
from t cross apply
     (values ('Sol25A', Sol25A), ('Sol25B', Sol25B), ('Sol25C', Sol25C),
             ('Sol40A', Sol40A), ('Sol40B', Sol40B)
     ) v(product, val)
group by product;

Convert column into the rows

1 Answers1