How to Transpose a Table with Multiple Columns in SQL Server?

SQL Server multi-column pivot method

Converting a table to its transposed form is useful in a variety of situations. This article focuses on how to transpose a table with multiple columns (A, B, etc.) in Microsoft SQL Server.

Pivot data using UNPIVOT and PIVOT

To transpose a table, you can use the UNPIVOT and PIVOT functions together:

• UNPIVOT: Convert multiple columns to rows, creating a new column named col, which contains the column names (A, B, etc.), and a value column containing the corresponding values.
• PIVOT: Convert col values ​​into column headers and summarize the value column for each header.

An example is as follows:

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

CROSS APPLY and VALUES methods for SQL Server 2008 and above

For SQL Server 2008 and above, you can also use CROSS APPLY combined with VALUES to unpack the data:

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

Apply to your query

If you want to transpose the results of the current query, you can use a similar method:

<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 transpose the ACalls and BCalls columns into rows and create column headers for each day of the week (Mon, Tue, etc.).

The above is the detailed content of How to Transpose a Table with Multiple Columns in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!