How to Pivot Multiple Columns in a T-SQL Table?

Pivot T-SQL Tables for Multiple Columns

In T-SQL, pivoting data can be a useful technique for transforming table structures to present data differently. When pivoting involves handling multiple columns, unpivoting the data may prove beneficial.

Consider the following table:

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 goal is to pivot this table to obtain the following desired result:

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

To achieve this, we can follow these steps:

1. Unpivot Result, Score, and Grade Columns:

   Use the UNPIVOT function or CROSS APPLY to unpivot the data:

   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)

2. Pivot the Unpivoted Data:

   Apply the PIVOT function to the unpivoted data:

   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;

This will yield the desired result, providing a table with multiple columns pivoted.

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