New to PIVOT in SQL

Question

I need help to find whether the below is achievable

I got a table for example

Is it possible to use the PIVOT sql command to convert it something like this If so could someone help me with it.

Ok just found out my version is Microsoft SQL Server 2000 - 8.00.2066 (Intel X86) and can't use pivot command :( — AK SQL, Jan 24 '17 at 15:20
Agree with GurV. A pivot would work if you are taking the second table and morphing it into the first, not the other way around. The first table is not normalized. — MikeS, Jan 24 '17 at 15:21
Any chance of upgrading SQL Server to something modern? EOL was nearly 4 years ago... — squillman, Jan 24 '17 at 15:23
Yes but that I cant make the decision it should be the boss..is there anyway doing this in 2000 — AK SQL, Jan 24 '17 at 15:24
Is that the full schema of your source table? Or are there more QX columns? — squillman, Jan 24 '17 at 15:27
on SQL 2000 you will need to do the unpivot by hand. How many questions do you have? — Matthew Whited, Jan 24 '17 at 15:35

score 2 · Accepted Answer · answered Jan 24 '17 at 15:40

without the UNPIVOT keyword you would have to do this transform by hand.

SELECT *
FROM (
    SELECT 
        [D].[UID]
        ,'Q1' AS [Question]
        ,[D].[Q1_Score] AS [Score]
        ,[D].[Category]
    FROM [DataSet] AS [D]
    UNION ALL
    SELECT 
        [D].[UID]
        ,'Q2' AS [Question]
        ,[D].[Q2_Score] AS [Score]
        ,[D].[Category]
    FROM [DataSet] AS [D]
    UNION ALL
    SELECT 
        [D].[UID]
        ,'Q3' AS [Question]
        ,[D].[Q3_Score] AS [Score]
        ,[D].[Category]
    FROM [DataSet] AS [D]
) [upivot]
ORDER BY
    [UID]
    ,[Question]

... if you can use a new version of SQL with UNPIVOT then you can do this...

SELECT 
    [UID]
    ,SUBSTRING([Question],1,2) AS [Question]
    ,[Score]
    ,[Category]
FROM [DataSet]
UNPIVOT (
    [Score] FOR [Question] IN (
        [Q1_Score]
        ,[Q2_Score]
        ,[Q3_Score]
    )
) [upivot]

The results for either of the above is this...

UID Question    Score   Category
1   Q1              10  Science
1   Q2              7   Science
1   Q3              5   Science
2   Q1              9   Maths
2   Q2              6   Maths
2   Q3              10  Maths
3   Q1              0   History
3   Q2              3   History
3   Q3              4   History
4   Q1              3   English
4   Q2              1   English
4   Q3              4   English

NathanAck · Answer 2 · 2017-01-24T15:49:13.823

1

You could create a new table using select with unions. If it needs to be the same table you could then drop the old one and create it from the new one.

select unique_id,'Q1' as 'Question', Q1_score as 'Score', Category
from table
union all
select unique_id,'Q2' as 'Question', Q2_score as 'Score', Category
from table
union all
select unique_id,'Q3' as 'Question', Q3_score as 'Score', Category
from table
order by unique_id, Question

--changed the group by to order by per Matthew Whited

edited Jan 24 '17 at 15:49

answered Jan 24 '17 at 15:37

NathanAck

351
3
9

you are going to need a subquery and are few aggragate functions to use that group by – Matthew Whited Jan 24 '17 at 15:45

score 0 · Answer 3 · edited May 23 '17 at 11:45

In this post of mine from just the other day, I demonstrate that the complex UNION ALL SELECT-s as seen above are not necessary - if you CROSS JOIN with an in-line table consisting of as many consecutive integers as you have horizontal columns to un-pivot. It works on any database that supports CROSS JOIN and the CASE .. WHEN clause:

How to create multiple rows from a initial row

vertical pivoting and "un-pivoting" mean the same.

And here, I take it to extremes:

Vertica - Is there LATERAL VIEW functionality?

In this post, I un-pivot a two-row table, then do something with the pivoted data, and then re-pivot it.

Happy playing ..

New to PIVOT in SQL

3 Answers3