Transpose a SQL table with multiple columns
Question:
You need to transpose a SQL table with multiple columns, for example:
1 2 3 4 5 6 7 | <code>Day A B
---------
Mon 1 2
Tue 3 4
Wed 5 6
Thu 7 8
Fri 9 0</code>
|
Copy after login
Transpose to the following format:
1 2 3 4 | <code>Value Mon Tue Wed Thu Fri
--------------------------
A 1 3 5 7 9
B 2 4 6 8 0</code>
|
Copy after login
Solution:
To transpose a table with multiple columns, you can use the UNPIVOT and PIVOT functions together.
-
UNPIVOT: Convert multiple columns (A, B) into rows and add a column for column names:
1 2 3 4 5 6 7 | <code class = "language-sql" >select day, col, value
from yourtable
unpivot
(
value
for col in (A, B)
) unpiv</code>
|
Copy after login
-
PIVOT: Convert "day" value to column and aggregate "value" column:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <code class = "language-sql" >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</code>
|
Copy after login
This will generate the required transposed table.
Additional notes:
- If your SQL Server version is 2008 or higher, you can use CROSS APPLY and VALUES instead of the UNPIVOT function to transpose the data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <code class = "language-sql" >select *
from
(
select day, col, value
from yourtable
cross apply
(
values ( 'A' , ACalls), ( 'B' , BCalls)
) c (col, value)
) src
pivot
(
max(value)
for day in (Mon, Tue, Wed, Thu, Fri)
) piv</code>
|
Copy after login
- To perform the transpose operation on your specific query, you can use code similar to the following structure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <code class = "language-sql" >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</code>
|
Copy after login
The above is the detailed content of How to Transpose a SQL Table with Multiple Columns?. For more information, please follow other related articles on the PHP Chinese website!