Transpose table like Proc Transpose in SAS

[jenny.rhodes]

Hello,

Please can anyone guide me on how to transpose an access table where I
have many records per id eg

UserID Question Answer
1 1 a
1 2 d
1 3 c
1 4 d
2 1 e
2 2 a
2 3 a
2 4 d

So that it looks like:

UserID Q1 Q2 Q3 Q4
1 a d c d
2 e a a d

You could do it in SAS by doing proc transpose by userid.
Any answers gratefully received.

[Helen Wheels]

je**********@il eo.com.au wrote:

Hello,

Please can anyone guide me on how to transpose an access table where I
have many records per id eg

UserID Question Answer
1 1 a
1 2 d
1 3 c
1 4 d
2 1 e
2 2 a
2 3 a
2 4 d

So that it looks like:

UserID Q1 Q2 Q3 Q4
1 a d c d
2 e a a d

You could do it in SAS by doing proc transpose by userid.
Any answers gratefully received.

Access doesn't have an exact equivalent of SAS proc transpose,
but in this case a crosstab query will do what you need. The
following SQL should work for you:

TRANSFORM Min(answer)
SELECT userid
FROM yourtable
GROUP BY userid
PIVOT question;

(this assumes that your table has at most one row per combination
of userid and question)
Cheers