How Can I Transpose a Multi-Column Table in SQL?

SQL Server Multiple List Transpose

Table transposition means converting rows into columns and columns into rows. This is especially useful when you need to pivot data for analysis or presentations.

Question:

You want to transpose a table with multiple columns from the following format:

Day	A	B
Mon	1	2
Tue	3	4
Wed	5	6
Thu	7	8
Fri	9	0

Convert to the following format:

Value	Mon	Tue	Wed	Thu	Fri
A	1	3	5	7	9
B	2	4	6	8	0

Solution:

To transpose multiple columns in SQL, you can use the UNPIVOT and PIVOT functions together.

1. UNPIVOT:

The UNPIVOT function denormalizes data by converting columns into rows. This creates a new table with three columns: day, col, and value.

<code class="language-sql">select day, col, value
from yourtable
unpivot
(
    value
    for col in (A, B)
) unpiv</code>

2. PIVOT:

The PIVOT function then renormalizes the data by converting the day values ​​into columns. This will produce the transposed table.

<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>

Note:

• The MAX aggregate function is used in PIVOT queries to ensure that duplicate values ​​are handled correctly.
• For SQL Server 2008 and later, you can use CROSS APPLY with VALUES syntax to reverse the data instead of UNPIVOT.

Apply to your query:

To transpose your specific query you can use the following modified code:

<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>

This will generate a transposed table in the desired format.

The above is the detailed content of How Can I Transpose a Multi-Column Table in SQL?. For more information, please follow other related articles on the PHP Chinese website!