How to Pivot Multiple Columns in T-SQL to Transform Financial Ratio Data?

Pivot Multiple Columns in TSQL

When working with a relational database, it is often necessary to transform data to make it easier to analyze or report on. One way to do this is to pivot the data, which involves rotating rows into columns and vice versa.

Consider the following table that contains financial ratios, results, scores, and grades:

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

To pivot this data and retain all the labels, we need to unpivot the result, score, and grade columns so that each row has one value in each column. This can be achieved using the UNPIVOT function or CROSS APPLY, depending on the version of SQL Server you are using.

Once the data is unpivoted, we can apply the PIVOT function to rotate the rows into columns. The syntax will be similar to the following:

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;

The resulting output will be a table with one row for each ratio, and columns for the current ratio, gearing ratio, performance ratio, total, score, and grade:

RATIO	CURRENT RATIO	GEARING RATIO	PERFORMANCE RATIO	TOTAL	SCORE	GRADE
grade	Good	Good	Satisfactory	Good	NULL	NULL
result	1.29400	0.33840	0.04270	NULL	NULL	NULL
score	60.00000	70.00000	50.00000	180.00000	NULL	NULL

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