MSSQL - Rows as Columns

Question

I have following Table:

+------+--------------+------------+--------+
|  ID  |    TestID    | QuestionId | Answer |
+------+--------------+------------+--------+
|   1  |      10      |     15     |    0   |
|   2  |      10      |     23     |    0   |
|   3  |      10      |     41     |    1   |
|   4  |      16      |     15     |    0   |
|   5  |      16      |     23     |    1   |
|   6  |      16      |     41     |    1   |
|   7  |      24      |     15     |    1   |
|   8  |      24      |     23     |    0   |
|   9  |      24      |     41     |    0   |
+------+--------------+------------+--------+

For internal reports I need following output:

+--------------+----------------+----------------+----------------+
|  QuestionId  |      Test_1    |      Test_2    |      Test_3    |
+--------------+----------------+----------------+----------------+
|      15      |        0       |        0       |        1       |
|      23      |        0       |        1       |        0       |
|      41      |        1       |        1       |        0       |
+--------------+----------------+----------------+----------------+

I have no idea how to do this. Do you have any suggestions?

Actually you want rows as columns, you can achieve this by using `PIVOT` operator — Valerica, Jan 22 '18 at 09:40
Is there a Test table with a score?. Is the number of tests fixed? — ste-fu, Jan 22 '18 at 09:42
probably this question help you https://stackoverflow.com/questions/13188158/transpose-pivot-distinct-row-attribute-as-column-and-group-another-attribute — Hasan Fathi, Jan 22 '18 at 09:48
Possible duplicate of [Transpose / Pivot distinct row attribute as column and group another attribute?](https://stackoverflow.com/questions/13188158/transpose-pivot-distinct-row-attribute-as-column-and-group-another-attribute) — Hasan Fathi, Jan 22 '18 at 09:49

score 3 · Accepted Answer · answered Jan 22 '18 at 09:46

Use Pivot

;WITH CTE
AS
(
    SELECT
        SeqNo = ROW_NUMBER() OVER(PARTITION BY QuestionId ORDER BY TestId),

        *
        FROM YourTable
)
SELECT
    QuestionId,
    Test_1 = MAX([1]),
    Test_2 = MAX([2]),
    Test_3 = MAX([3])
    FROM CTE
    PIVOT
    (
        SUM(Answer)
        FOR SeqNo in
        (
            [1],[2],[3]
        )
    )P
    GROUP BY QuestionId

My Result

score 1 · Answer 2 · answered Jan 22 '18 at 09:46

Here is one way using conditional aggregation and Row_number window function

select QuestionId, 
      Test_1 = max(case when rn = 1 then Answer end),
      Test_2 = max(case when rn = 2 then Answer end),
      Test_3 = max(case when rn = 3 then Answer end)
from (select *, Rn = row_number()over(partition by QuestionId order by TestID)
      from table1) a
group by QuestionId

if the number of tests is unknown then you have to employ dynamic sql

score 1 · Answer 3 · answered Jan 22 '18 at 09:46

One idea:

USE Sandbox;
GO

CREATE TABLE #Test (ID int, TestID int, QuestionID int, Answer tinyint);

INSERT INTO #Test
VALUES (1,10,15,0),
       (2,10,23,0),
       (3,10,41,1),
       (4,16,15,0),
       (5,16,23,1),
       (6,16,41,1),
       (7,24,15,1),
       (8,24,23,0),
       (9,24,41,0);
GO

WITH Rnks AS (
    SELECT *,
           DENSE_RANK() OVER (ORDER BY TestID) AS TestNum
    FROM #Test T)
SELECT QuestionID,
       MAX(CASE WHEN TestNum = 1 THEN Answer END) AS Test_1,
       MAX(CASE WHEN TestNum = 2 THEN Answer END) AS Test_2,
       MAX(CASE WHEN TestNum = 3 THEN Answer END) AS Test_3
FROM Rnks
GROUP BY QuestionID;

GO
DROP TABLE #Test;

This, however, assumes there are a limited number of tests (and I have guessed the definition of the test_x correctly). If you have an indeterminable amount of tests, you'll need to use dynamic SQL.

MSSQL - Rows as Columns

3 Answers3