How to Transpose Dynamic SQL Server Columns to Rows Using UNPIVOT?

Transpose Dynamic SQL Server Columns to Rows Using UNPIVOT

In SQL Server, the UNPIVOT function allows for transposing columns into rows, a technique often referred to as unpivoting. This is particularly useful when working with data that has a dynamic or variable number of columns.

Example Scenario

Consider the following Table1:

Table1
-----------------------------------------
Id       abc  brt ccc ddq eee fff gga hxx
-----------------------------------------
12345     0    1   0   5   0   2   0   0  
21321     0    0   0   0   0   0   0   0   
33333     2    0   0   0   0   0   0   0   
41414     0    0   0   0   5   0   0   1   
55001     0    0   0   0   0   0   0   2   
60000     0    0   0   0   0   0   0   0 
77777     9    0   3   0   0   0   0   0

The goal is to unpivot Table1 into the following Expected_Result_Table, considering only values greater than 0:

Expected_Result_Table
---------------------
Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

Solution Using UNPIVOT

The UNPIVOT function takes a table with multiple columns and transforms it into a wider table with two additional columns: a "value" column containing the data from the original table's columns and a "name" column identifying the source column.

SELECT Id,
       name AS Word,
       value AS Qty>0
FROM Table1
UNPIVOT (value FOR name IN (abc, brt, ccc, ddq, eee, fff, gga, hxx)) AS unpvt
WHERE value > 0;

This query will generate the expected result.

The above is the detailed content of How to Transpose Dynamic SQL Server Columns to Rows Using UNPIVOT?. For more information, please follow other related articles on the PHP Chinese website!