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.

Answer 1

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