-2

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!

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.