Search code examples
sqlms-accessrowscol

SQL transform rows into columns

My goal is to transform this:

table1:

ID  Day Status
101 1   N
101 2   P
101 3   N
101 4   P
101 5   R
102 1   P
102 2   P
102 3   R
102 4   R
102 5   R
103 2   N
103 3   N
103 4   P
103 5   P
104 3   P
104 4   P
104 5   R
105 4   N
105 5   N

Into this:

table2:

ID  1   2   3   4   5
101 N   P   N   P   R
102 P   P   R   R   R
103     N   N   P   P
104         P   P   R
105             N   N

Is this possible?

Solution

  • In Access, you could use:

    TRANSFORM First([Status]) AS Status
SELECT [ID]
FROM Table1
GROUP BY [ID]
PIVOT [Day];