TSQL PIVOT MULTIPLE COLUMNS
Since you want to pivot multiple columns of data, I would first suggest unpivoting the result
, score
and grade
columns so you don't have multiple columns but you will have multiple rows.
Depending on your version of SQL Server you can use the UNPIVOT function or CROSS APPLY. The syntax to unpivot the data will be similar to:
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)
See SQL Fiddle with Demo. Once the data has been unpivoted, then you can apply the PIVOT function:
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;
See SQL Fiddle with Demo. This will give you the result:
| 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 |
Uchenna Ebilah
Updated on July 09, 2022Comments
-
Uchenna Ebilah almost 2 years
I have the following table but unsure of whether it is possible to pivot this and retain all the labels.
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
I will admit to not being very good with the use of pivots, so after several attempts resulting in this output:
SELECT 'RESULT' AS 'Ratio' ,[Current Ratio] AS 'Current Ratio' ,[Gearing Ratio] AS 'Gearing Ratio' ,[Performance Ratio] AS 'Performance Ratio' ,[TOTAL] AS 'TOTAL' FROM ( SELECT RATIO, RESULT FROM GRAND_TOTALS ) AS SREC PIVOT ( MAX(RESULT) FOR RATIO IN ([Current Ratio],[Gearing Ratio], [Performance Ratio], [TOTAL]) ) AS PVT
This gives the result:
Ratio Current Ratio Gearing Ratio Performance Ratio Result 1.294 0.3384 0.0427
I will admit to be feeling very stumped on what to do next to produce the result I need which is:
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
-
Der U over 10 yearsi'll allways be impressed by PIVOT solutions of true experts! I found this here: pratchev.blogspot.de/2009/01/pivoting-on-multiple-columns.html . In the second pragraph the author suggests two PIVOT clauses in one statement. is there a downside to this?
-
Taryn over 10 years@DerU It really depends, I personally prefer to unpivot so the data is in an easier format then applying two pivots.
-
Raymondo about 4 years@DerU - I'm an huge fan of the approach in the article you have attached. I'm also in the same boat Taryn in that it does depend. I'll always run a "set statistics and time" and if anything looks untoward try another approach
-
Merin Nakarmi almost 3 years@DerU So cool of you for sharing that link! It is useful!