How to Transpose Tables in SQL Using UNPIVOT and PIVOT?

SQL table transposition: application of UNPIVOT and PIVOT functions

Transposing a table in SQL can be achieved using a combination of UNPIVOT and PIVOT functions. The UNPIVOT function converts columns to rows, while the PIVOT function converts rows to columns.

UNPIVOT data

UNPIVOT function is used to convert multiple columns into a single column. In the example, columns A and B need to be expanded. The following query demonstrates this process:

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

This query will create a new table with three columns: day, col and value. The col column will contain the column name (A or B) and the value column will contain the corresponding value from the expanded column.

PIVOT data

PIVOT function is used to convert rows into columns. In this example, the day values ​​(Mon, Tue, Wed, Thu, Fri) need to be converted into columns:

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

The final query will produce the desired output:

<code>Value | Mon | Tue | Wed | Thu | Fri
-------|-----|-----|-----|-----|-----
A      | 1   | 3   | 5   | 7   | 9
B      | 2   | 4   | 6   | 8   | 0</code>

Apply solution to your query

To apply this solution to your specific query, you can modify it as follows:

<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 query will transform the data in your DataTable into the desired format.

The above is the detailed content of How to Transpose Tables in SQL Using UNPIVOT and PIVOT?. For more information, please follow other related articles on the PHP Chinese website!