SQL transpose full table
pivot, sql, sql-server, unpivot
Solution
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
Problem
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.