I need to do the following transpose in MS SQL
from:
Day A B
---------
Mon 1 2
Tue 3 4
Wed 5 6
Thu 7 8
Fri 9 0
To the following:
Value Mon Tue Wed Thu Fri
--------------------------
A 1 3 5 7 9
B 2 4 6 8 0
I understand how to do it with PIVOT
when there is only one column (A) but I can not figure out how to do it when there are multiple columns to transpose (A,B,...)
Example code to be transposed:
select LEFT(datename(dw,datetime),3) as DateWeek,
sum(ACalls) as A,
Sum(BCalls) as B
from DataTable
group by LEFT(datename(dw,datetime),3)
Table Structure:
Column DataType
DateTime Datetime
ACalls int
BCalls int
Any help will be much appreciated.
2条答案
按热度按时间ktca8awb1#
In order to transpose the data into the result that you want, you will need to use both the
UNPIVOT
and thePIVOT
functions.The
UNPIVOT
function takes theA
andB
columns and converts the results into rows. Then you will use thePIVOT
function to transform theday
values into columns:See SQL Fiddle with Demo.
If you are using SQL Server 2008+, then you can use
CROSS APPLY
withVALUES
to unpivot the data. You code would be changed to the following:See SQL Fiddle with Demo.
Edit #1, applying your current query into the above solution you will use something similar to this:
a2mppw5e2#