How to Pivot Multiple Columns in T-SQL to Reshape Data for Reporting?

Pivoting Multiple Columns in TSQL

When working with relational databases, it is often necessary to reshape data for reporting purposes. Pivoting is a technique that can be utilized to transform data from a row-oriented structure to a column-oriented one. This article demonstrates how to pivot multiple columns in a table while retaining all the associated labels.

Challenge

Consider the following table named GRAND_TOTALS:

RATIO	RESULT	SCORE	GRADE
Current Ratio	1.294	60	Good
Gearing Ratio	0.3384	70	Good
Performance Ratio	0.0427	50	Satisfactory
TOTAL	NULL	180	Good

The objective is to pivot the RESULT, SCORE, and GRADE columns, resulting in a table with the following format:

RATIO	Current Ratio	Gearing Ratio	Performance Ratio	TOTAL
Result	1.294	0.3384	0.0427	NULL
Score	60	70	50	180
Grade	Good	Good	Satisfactory	Good

Solution

To pivot multiple columns effectively, consider the following steps:

1. Unpivot the Result, Score, and Grade Columns:

   Unpivoting involves converting multiple columns into a single column while keeping track of the original column names. This can be achieved using the UNPIVOT clause. The syntax below demonstrates how to unpivot the columns:

   select ratio, col, value
   from GRAND_TOTALS
   cross apply
   (
     select 'result', cast(result as varchar(10)) union all
     select 'score', cast(score as varchar(10)) union all
     select 'grade', grade
   ) c(col, value)

   This query will create a new table with the RATIO, COL, and VALUE columns, where COL represents the original column name (result, score, or grade) and VALUE contains the corresponding values.

2. Apply the PIVOT Function:

   Once the data is unpivoted, the PIVOT function can be applied to transform the data into the desired format. The syntax for pivoting is as follows:

   select ratio = col,
     [current ratio], [gearing ratio], [performance ratio], total
   from
   (
     select ratio, col, value
     from GRAND_TOTALS
     cross apply
     (
       select 'result', cast(result as varchar(10)) union all
       select 'score', cast(score as varchar(10)) union all
       select 'grade', grade
     ) c(col, value)
   ) d
   pivot
   (
     max(value)
     for ratio in ([current ratio], [gearing ratio], [performance ratio], total)
   ) piv;

   In this query, the PIVOT function groups the rows by the RATIO column and pivots the data using the COL values as the column headers. The MAX aggregate function is used to retrieve the latest value for each row.

Result

Executing the above query will produce the desired pivot table:

RATIO	Current Ratio	Gearing Ratio	Performance Ratio	TOTAL
grade	Good	Good	Satisfactory	Good
result	1.29400	0.33840	0.04270	NULL
score	60.00000	70.00000	50.00000	180.00000

This table retains all the necessary labels and successfully pivots the desired columns.

The above is the detailed content of How to Pivot Multiple Columns in T-SQL to Reshape Data for Reporting?. For more information, please follow other related articles on the PHP Chinese website!