SQL Matrix Help

[cotton31]

Hi,

I currently have a table that looks like this:

[Question 1] [Question 2] [Question 3] [Question 4]
Answer A Answer C Answer A Answer B
Answer B Answer B Answer C Answer A
Answer A Answer A Answer C Answer B
Answer C Answer A Answer C Answer B
Answer C Answer C Answer B Answer B

I need to somehow pivot this so that it looks like this:

Answer A Answer B Answer C
[Question 1] 2 1 2
[Question 2] 2 1 2
[Question 3] 1 1 3
[Question 4] 1 4 0

How could I do this? I imagine my indenting will go crappy when I post this so it's more difficult to read....

Cheers!

 

[cotton31]

That's brilliant mate! Just the job.

Thanks

 

[cotton31]

Slight thread revival!

Same sort of thing, although now I have a list of questions and answers (not dissimilar to the UNPIVOT example shown by s-p above) and I need the question text to become the column name or header, and the related answer to show in each column.

There can be an unlimited number of questions and I wouldn't necessarily know the question text.

Can you help?