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
You can create multiple columns or rows in a pivot table to handle multiple descriptions.
Pivoting is a technique used to rotate(transpose) rows to columns. It turns the unique values from one column in one table or table expression into multiple columns in another table. SQL Server 2005 introduced the PIVOT operator as a syntax extension for table expression in the FROM clause.
Add an Additional Row or Column Field Click any cell in the PivotTable. The PivotTable Fields pane appears. You can also turn on the PivotTable Fields pane by clicking the Field List button on the Analyze tab. Click and drag a field to the Rows or Columns area.
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 |
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With