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.
In order to transpose the data into the result that you want, you will need to use both the UNPIVOT
and the PIVOT
functions.
The UNPIVOT
function takes the A
and B
columns and converts the results into rows. Then you will use the PIVOT
function to transform the day
values into columns:
select *
from
(
select day, col, value
from yourtable
unpivot
(
value
for col in (A, B)
) unpiv
) src
pivot
(
max(value)
for day in (Mon, Tue, Wed, Thu, Fri)
) piv
See SQL Fiddle with Demo.
If you are using SQL Server 2008+, then you can use CROSS APPLY
with VALUES
to unpivot the data. You code would be changed to the following:
select *
from
(
select day, col, value
from yourtable
cross apply
(
values ('A', A),('B', B)
) c (col, value)
) src
pivot
(
max(value)
for day in (Mon, Tue, Wed, Thu, Fri)
) piv
See SQL Fiddle with Demo.
Edit #1, applying your current query into the above solution you will use something similar to this:
select *
from
(
select LEFT(datename(dw,datetime),3) as DateWeek,
col,
value
from DataTable
cross apply
(
values ('A', ACalls), ('B', BCalls)
) c (col, value)
) src
pivot
(
sum(value)
for dateweek in (Mon, Tue, Wed, Thu, Fri)
) piv