By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,948 Members | 1,857 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,948 IT Pros & Developers. It's quick & easy.

Transpose table like Proc Transpose in SAS

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
je**********@ileo.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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.