Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL PIVOT MULTIPLE COLUMNS

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 
like image 382
Uchenna Ebilah Avatar asked Oct 25 '13 13:10

Uchenna Ebilah


People also ask

Can you PIVOT on multiple columns?

You can create multiple columns or rows in a pivot table to handle multiple descriptions.

Can we PIVOT 2 columns in SQL?

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.

How do I create a pivot table with multiple columns?

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.


1 Answers

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 | 
like image 149
Taryn Avatar answered Sep 18 '22 14:09

Taryn