SQL Matrix Help

cotton31 · 12 Mar 2012 at 15:06

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!

s-p · 12 Mar 2012 at 22:04

Table creation:

Code:

CREATE TABLE [dbo].[Table1]
(
    [Question1] NCHAR(10) NOT NULL , 
    [Question2] NCHAR(10) NOT NULL, 
    [Question3] NCHAR(10) NOT NULL, 
    [Question4] NCHAR(10) NOT NULL
)

Data insert:

Code:

INSERT INTO [dbo].[Table1] ([Question1], [Question2], [Question3], [Question4]) VALUES (N'A         ', N'C         ', N'A         ', N'B         ')
INSERT INTO [dbo].[Table1] ([Question1], [Question2], [Question3], [Question4]) VALUES (N'B         ', N'B         ', N'C         ', N'A         ')
INSERT INTO [dbo].[Table1] ([Question1], [Question2], [Question3], [Question4]) VALUES (N'A', N'A', N'C', N'B')
INSERT INTO [dbo].[Table1] ([Question1], [Question2], [Question3], [Question4]) VALUES (N'C         ', N'A         ', N'C         ', N'B         ')
INSERT INTO [dbo].[Table1] ([Question1], [Question2], [Question3], [Question4]) VALUES (N'C', N'C', N'B', N'B')

Initial Selection:

Code:

SELECT [Question1], [Question2], [Question3], [Question4] FROM dbo.Table1

Question1	Question2	Question3	Question4
A         	C         	A         	B         
B         	B         	C         	A         
A         	A         	C         	B         
C         	A         	C         	B         
C         	C         	B         	B

Unpivot Selection:

Code:

SELECT CAST (QuestionID AS NCHAR) AS QuestionID, Answer
FROM (SELECT [Question1], [Question2], [Question3], [Question4] FROM dbo.Table1) AS p
UNPIVOT (Answer FOR QuestionID IN ([Question1], [Question2], [Question3], [Question4])) AS s

QuestionID	Answer
Question1       A         
Question2       C         
Question3       A         
Question4       B         
Question1       B                     
...etc.

Unpivot + Pivot Selection:

Code:

SELECT QuestionID, [A], [B], [C]
FROM 
(SELECT CAST (QuestionID AS NCHAR) AS QuestionID, Answer
FROM (SELECT [Question1], [Question2], [Question3], [Question4] FROM dbo.Table1) AS p
UNPIVOT (Answer FOR QuestionID IN ([Question1], [Question2], [Question3], [Question4])) AS s) AS pivTemp
PIVOT
	(COUNT(Answer)
	FOR Answer IN ([A], [B], [C])
) As PivTable

QuestionID	A	B	C
Question1 	2	1	2
Question2 	2	1	2
Question3 	1	1	3
Question4 	1	4	0

How's that?

cotton31 · 13 Mar 2012 at 14:55

That's brilliant mate! Just the job.

Thanks

cotton31 · 24 May 2012 at 15:32

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?