How to Pivot Multiple Columns in T-SQL Efficiently?

How to Pivot Multiple Columns in TSQL

In TSQL, pivoting a table involves rearranging rows into columns. However, when dealing with multiple columns, it's essential to retain all labels.

Unpivoting Data

To begin, since we have multiple data columns, it's recommended to unpivot them into a single column. This can be achieved using either the UNPIVOT function or the CROSS APPLY method, depending on the SQL Server version. The syntax for unpivoting would be:

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)

Performing the Pivot

Once the data is unpivoted, you can apply the PIVOT function to rearrange the columns as desired. The syntax would be:

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;

Result

This will produce the desired output:

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

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