SQL Server 2008 pivot without aggregate

Question

I have table to test score data that I need to pivot and I am stuck on how to do it.

I have the data as this:

gradelistening speaking reading writing
0   0.0   0.0   0.0   0.0
1 399.4 423.8   0.0   0.0
2 461.6 508.4 424.2 431.5
3 501.0 525.9 492.8 491.3
4 521.9 517.4 488.7 486.7
5 555.1 581.1 547.2 538.2
6 562.7 545.5 498.2 530.2
7 560.5 525.8 545.3 562.0
8 580.9 548.7 551.4 560.3
9 602.4 550.2 586.8 564.1
10 623.4 581.1 589.9 568.5
11 633.3 578.3 598.1 568.2
12 626.0 588.8 600.5 564.8

But I need it like this:

  gr0 gr1 gr2 gr3 gr4 gr5 gr6 gr7 ...
listening 0.0 399.4 461.6 501.0 521.9 555.1 562.7 560.5 580.9...
speaking 0.0 423.8...
reading 0.0 0.0 424.2...
writing 0.0 0.0 431.5...

I don't need to aggregate anything, just pivot the data.

Are the number of grades fixed (at 12/13 in this example) or variable? — Dan, Mar 22 '10 at 16:47
Yes, the number of grades is fixed. The dataset is padded with zeroed records when scores are not available, so there will always be 13 records for the query. — Bryan Lewis, Mar 22 '10 at 16:48

score 2 · Accepted Answer · answered Mar 22 '10 at 17:54

The following is one way to solve the problem, but I am not sure if it is the most efficient.

DECLARE @PivotData table(grade int, listening float, speaking float, reading float, writing float)
INSERT into @PivotData
SELECT 0, 0.0, 0.0, 0.0, 0.0 UNION ALL
SELECT 1, 399.4, 423.8, 0.0, 0.0 UNION ALL
SELECT 2, 461.6, 508.4, 424.4, 431.5 UNION ALL
SELECT 3, 501.0, 525.9, 492.8, 491.3

SELECT TestType, [0] As gr0, [1] as gr1, [2] as gr2, [3] as gr3
FROM
(
    SELECT grade, TestType, score
    FROM 
    (
        SELECT grade, listening, speaking, reading, writing from @PivotData
    ) PivotData
    UNPIVOT
    (
        score for TestType IN (listening, speaking, reading, writing)
    ) as initialUnPivot
) as PivotSource
PIVOT 
(
    max(score) FOR grade IN ([0], [1], [2], [3])
) as PivotedData

Basically what I did was to initially unpivot the data to get a table that contains the grade, testtype, and score each in its own column, then I pivoted the data to get the answer you want. The fact that my UnPivoted source data contains the TestType column makes it so that each combination of grade and testype returns a single score, so all aggregations will just return that particular score for the combination and will not perform anything on it.

I have only done it for the first 4 grades, but I am pretty sure you can tell what you need to add to have it work for all 13 grades.

Thanks. This worked. I simply changed the "from @PivotData" to the name of my table and added a WHERE clause just after it and all went smoothly. Adding in the remaining grades was no problem. — Bryan Lewis, Mar 22 '10 at 19:55

score 0 · Answer 2 · answered Mar 22 '10 at 17:45

Here is a solution. The code below uses Oracle's dual table to create a dummy table for the areas (e.g., listening, speaking, etc.); however, for SQLServer, I believe you can just truncate the 'from dual' clause within each union. The query performs a cartesian product in order to pull down the column-oriented grades into a normalized structure (columns skill, grade, and score). This is then used in the normal manner to pivot the data. I also added a "rank" column so the data could be sorted as per the results you specified.

select skill, rank
  , max(case grade when 0 then score else null end) gr0
  , max(case grade when 1 then score else null end) gr1
  , max(case grade when 2 then score else null end) gr2    
from (
  select skill, rank, grade
    , case skill when 'listening' then listening
                when 'speaking' then speaking
                when 'reading' then reading
                when 'writing' then writing end score
  from tmp_grade t,  (
    select 'listening' skill, 1 rank from dual
    union (select 'speaking', 2 from dual)
    union (select 'reading', 3 from dual)
    union (select 'writing', 4 from dual)
  ) area1
)
group by skill, rank
order by rank;

SQL Server 2008 pivot without aggregate

2 Answers2