group data for survey - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

group data for survey

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-02-05 : 04:07:45

hi,

i'm having following data:


create table survey
(
clientID int not null,
questionID int not null,
Answer int not null
) 

insert into survey (clientID, questionID, Answer) values (1, 1, 1)
insert into survey (clientID, questionID, Answer) values (1, 1, 2)
insert into survey (clientID, questionID, Answer) values (1, 1, 4)
insert into survey (clientID, questionID, Answer) values (1, 1, 5)
insert into survey (clientID, questionID, Answer) values (2, 1, 1)
insert into survey (clientID, questionID, Answer) values (2, 1, 5)
insert into survey (clientID, questionID, Answer) values (2, 1, 8)
insert into survey (clientID, questionID, Answer) values (3, 1, 2)
insert into survey (clientID, questionID, Answer) values (3, 1, 4)
insert into survey (clientID, questionID, Answer) values (3, 1, 6)
insert into survey (clientID, questionID, Answer) values (3, 1, 9)
insert into survey (clientID, questionID, Answer) values (4, 1, 8)
insert into survey (clientID, questionID, Answer) values (5, 1, 1)
insert into survey (clientID, questionID, Answer) values (5, 1, 5)
insert into survey (clientID, questionID, Answer) values (5, 1, 9)

and i want to get it out like this:

ClientID Q1_1 Q1_2 Q1_3 Q1_4
----------------------------------------
1 |1 |2 |4 |5 |
2 |1 |5 |8 |0 |
3 |2 |4 |6 |9 |
4 |8 |0 |0 |0 |
5 |1 |5 |9 |0 |

but i get the following output:

clientID Q1_1 Q1_2 Q1_3 Q1_4
----------- ----------- ----------- ----------- -----------
1 12 12 12 12
2 14 14 14 14
3 21 21 21 21
4 8 8 8 8
5 15 15 15 15

(5 row(s) affected)

with this code:



SELECT

	distinct(clientID)
	,Q1_1
	,Q1_2
	,Q1_3
	,Q1_4


from 
	(select 
		distinct(clientID)
	,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_1
	,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_2
	,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_3
	,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_4


		from
			dbo.survey

	group by clientid) as x

group by
	 x.clientID
	,x.Q1_1
	,x.Q1_2
	,x.Q1_3
	,x.Q1_4


order by clientID asc

what am i doing wrong? Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 04:20:32

use this:-

;
With Your_CTE (RowNo,ClientID,QuestionID,Answer) AS
(SELECT ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY Answer),
clientID, questionID, Answer
FROM survey
)

SELECT t1.ClientID,
ISNULL(t2.Answer,0) AS Q_1_1,
ISNULL(t3.Answer,0)AS Q_1_2,
ISNULL(t4.Answer,0)AS Q_1_3,
ISNULL(t5.Answer,0)AS Q_1_4
FROM (SELECT DISTINCT ClientID FROM Your_CTE) t1
OUTER APPLY (SELECT Answer FROM Your_CTE WHERE RowNo=1 AND ClientID=t1.ClientID)t2
OUTER APPLY (SELECT Answer FROM Your_CTE WHERE RowNo=2 AND ClientID=t1.ClientID)t3
OUTER APPLY (SELECT Answer FROM Your_CTE WHERE RowNo=3 AND ClientID=t1.ClientID)t4
OUTER APPLY (SELECT Answer FROM Your_CTE WHERE RowNo=4 AND ClientID=t1.ClientID)t5

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-02-05 : 04:22:16

Thanks, is there anything that works on SQL server 2000?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 04:34:43

sql 2000 compatible soln:-

DECLARE @temp table
(RowNo int,
clientID int,
questionID int,
Answer int
)

INSERT INTO @temp (RowNo,clientID, questionID, Answer)
SELECT (SELECT COUNT(*) + 1 FROM survey WHERE clientId=s.clientId and Answer < s.Answer),
clientID, questionID, Answer
FROM survey s
ORDER BY clientID,Answer

SELECT t1.clientID,
 ISNULL(t2.Answer,0) AS Q_1_1,
ISNULL(t3.Answer,0)AS Q_1_2,
ISNULL(t4.Answer,0)AS Q_1_3,
ISNULL(t5.Answer,0)AS Q_1_4      
FROM (SELECT DISTINCT clientID FROM @temp) t1
LEFT OUTER JOIN @temp t2
ON t2.clientID=t1.clientID
AND t2.RowNo=1
LEFT OUTER JOIN @temp t3
ON t3.clientID=t1.clientID
AND t3.RowNo=2
LEFT OUTER JOIN @temp t4
ON t4.clientID=t1.clientID
AND t4.RowNo=3
LEFT OUTER JOIN @temp t5
ON t5.clientID=t1.clientID
AND t5.RowNo=4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 04:46:54

even simpler (removed all joins)

DECLARE @temp table
(RowNo int,
clientID int,
questionID int,
Answer int
)

INSERT INTO @temp (RowNo,clientID, questionID, Answer)
SELECT (SELECT COUNT(*) + 1 FROM survey WHERE clientId=s.clientId and Answer < s.Answer),
clientID, questionID, Answer
FROM survey s
ORDER BY clientID,Answer

SELECT clientID,
  SUM(CASE WHEN RowNo=1 THEN Answer ELSE 0 END) AS Q_1_1,
SUM(CASE WHEN RowNo=2 THEN Answer ELSE 0 END) AS Q_1_2,
SUM(CASE WHEN RowNo=3 THEN Answer ELSE 0 END) AS Q_1_3,
SUM(CASE WHEN RowNo=4 THEN Answer ELSE 0 END) AS Q_1_4
FROM @temp
GROUP BY clientID

output
-----------------------
clientID    Q_1_1       Q_1_2       Q_1_3       Q_1_4
----------- ----------- ----------- ----------- -----------
1           1           2           4           5
2           1           5           8           0
3           2           4           6           9
4           8           0           0           0
5           1           5           9           0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 04:55:01

quote:

Originally posted by slimt_slimt


SELECT

	distinct(clientID)
	,Q1_1
	,Q1_2
	,Q1_3
	,Q1_4
from 
	(select 
		distinct(clientID)
	,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_1
	,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_2
	,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_3
	,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_4
		from
			dbo.survey
	group by clientid) as x
group by
	 x.clientID
	,x.Q1_1
	,x.Q1_2
	,x.Q1_3
	,x.Q1_4
order by clientID asc

what am i doing wrong?

There is nothing wrong with the query besides the fact you are summing question 1 for all columns...

	,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_1
	,sum(case when QuestionID = 1 2 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_2
	,sum(case when QuestionID = 1 3 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_3
	,sum(case when QuestionID = 1 4 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_4

E 12°55'05.25"
N 56°04'39.16"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-02-05 : 05:14:46

visakh16: lovely. thank you very much

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-02-05 : 05:33:39

Problem begins if i add questionID = 2


insert into survey (clientID, questionID, Answer) values (1, 2, 6)
insert into survey (clientID, questionID, Answer) values (1, 2, 7)
insert into survey (clientID, questionID, Answer) values (1, 2, 8)
insert into survey (clientID, questionID, Answer) values (2, 2, 3)
insert into survey (clientID, questionID, Answer) values (2, 2, 4)
insert into survey (clientID, questionID, Answer) values (2, 2, 5)
insert into survey (clientID, questionID, Answer) values (2, 2, 8)
insert into survey (clientID, questionID, Answer) values (3, 2, 3)
insert into survey (clientID, questionID, Answer) values (3, 2, 4)
insert into survey (clientID, questionID, Answer) values (4, 2, 3)
insert into survey (clientID, questionID, Answer) values (4, 2, 4)
insert into survey (clientID, questionID, Answer) values (4, 2, 6)
insert into survey (clientID, questionID, Answer) values (5, 2, 4)

and the RowNo collapses. :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 05:37:28

change insert temp step as follows:-

INSERT INTO @temp (RowNo,clientID, questionID, Answer)
SELECT (SELECT COUNT(*) + 1 FROM survey WHERE clientId=s.clientId and questionID=s.questionID
and Answer < s.Answer),
clientID, questionID, Answer
FROM survey s
ORDER BY clientID,questionID,Answer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 09:44:53

full soln:-

DECLARE @temp table
(RowNo int,
clientID int,
questionID int,
Answer int
)

INSERT INTO @temp (RowNo,clientID, questionID, Answer)
SELECT (SELECT COUNT(*) + 1 FROM survey WHERE clientId=s.clientId
and questionID=s.questionID
and Answer < s.Answer),
clientID, questionID, Answer
FROM survey s
ORDER BY clientID,Answer

SELECT clientID, questionID,
SUM(CASE WHEN RowNo=1 THEN Answer ELSE 0 END) AS Q_1_1,
SUM(CASE WHEN RowNo=2 THEN Answer ELSE 0 END) AS Q_1_2,
SUM(CASE WHEN RowNo=3 THEN Answer ELSE 0 END) AS Q_1_3,
SUM(CASE WHEN RowNo=4 THEN Answer ELSE 0 END) AS Q_1_4
FROM @temp
GROUP BY clientID, questionID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources