MS Access SQL multiple rows into one row with new columns [duplicate]

Question

I have a table which contains individual test items (responses) from a 20 item test. There is a Person_ID, a Item_Number, and a Item_Response. Example:

Person_ID| Item_Number| Item_Response
1        | 1          | 6
1        | 2          | 9
1        | 3          | 3
1        | 4          | 2
2        | 1          | 5
2        | 2          | 5
2        | 3          | 1
2        | 4          | 8
3        | 1          | 1
3        | 2          | 3
3        | 3          | 5
3        | 4          | 2

Need to transform to this with an SQL query:

Person_ID| Resp1| Resp2| Resp3| Resp4
1        | 6    | 9    | 3    | 2
2        | 5    | 5    | 1    | 8
3        | 1    | 3    | 5    | 2

I am using MS Access SQL

Thank you!

Answer 1

I have done something similar recently to "flatten" some data.

I found it easiest to use CASE expressions:

    SELECT Person_ID,
    SUM(CASE WHEN Item_Number=1 THEN Item_Response ELSE 0 END) AS Resp1,
    SUM(CASE WHEN Item_Number=2 THEN Item_Response ELSE 0 END) AS Resp2,
    SUM(CASE WHEN Item_Number=3 THEN Item_Response ELSE 0 END) AS Resp3,
    SUM(CASE WHEN Item_Number=4 THEN Item_Response ELSE 0 END) AS Resp4

    FROM tablename

    GROUP BY Person_ID;

That is standard SQL. In MS Access you would probably need to replace CASE with IIF():

    SELECT Person_ID,
    SUM(IIF(Item_Number=1, Item_Response, 0)) AS Resp1,
    SUM(IIF(Item_Number=2, Item_Response, 0)) AS Resp2,
    SUM(IIF(Item_Number=3, Item_Response, 0)) AS Resp3,
    SUM(IIF(Item_Number=4, Item_Response, 0)) AS Resp4

    FROM tablename

    GROUP BY Person_ID;

Answer 2

Assuming your table is MyTable, you just have to create a query with this statement:

TRANSFORM First(MyTable.Item_Response) AS FirstOfItem_Response
SELECT MyTable.Person_ID
FROM MyTable
GROUP BY MyTable.Person_ID
PIVOT "Resp" & MyTable.Item_Number;