SQLServer 2008 Pivot

Question

I need to show some information in a graph, the data is held in a SQL Server 2008 table. The graph is expecting 2 columns, one for QuestionNumber and the other for Score.

The table containing the data has column names that correspond to the question numbers ie A1, A2, A3, A4, B1, B2, B3, B4, C1, C2. Each question is given a score of 1 to 5. I need to show a graph where the X axis shows A1, A2, A3 etc and the Y axis shows the score.

I'm thinking I somehow need to rotate the data to achive this, but I'm not sure how. Maybe a different technique can achieve this rather than a pivot, so I'm open to any ideas.

Answer 1

UNPIVOT may work for you, assuming your input table MyTable has columns ID, A1, A2, A3, A4,A5:

SELECT id, QUESTION, ANSWER
FROM 
    (SELECT ID, A1, A2, A3, A4, A5
    FROM MyTable) AS p
UNPIVOT
    (ANSWER FOR QUESTION IN 
        (A1, A2, A3, A4, A5)
)AS unpvt
GO