Help with a cross-tab result set..

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

SQL Server 2000 Forums

Transact-SQL (2000)

Help with a cross-tab result set..

Author

Topic

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-06-22 : 18:55:14

help....
I have these three tables:

CREATE TABLE [surveyAnswer] (
	[submissionId] [int] NOT NULL ,
	[questionId] [int] NOT NULL ,
	[answer] [varchar] NULL 
) ON [PRIMARY]
GO
CREATE TABLE [surveySubmission] (
	[submissionId] [int] IDENTITY (1, 1) NOT NULL ,
	[surveyId] [int] NOT NULL ,
	[email] [varchar] (50)  NULL ,
	[submissionTimestamp] AS (getdate()) 
) ON [PRIMARY]
GO
CREATE TABLE [surveyQuestion] (
	[surveyId] [int] NOT NULL ,
	[sortOrder] [int] NOT NULL ,
	[questionId] [int] IDENTITY (1, 1) NOT NULL ,
	[question] [varchar] (200)  NOT NULL 
) ON [PRIMARY]
GO

I need to select from these 3 tables and I want my result set to look like this:

columns are:
email, question1, question2, etc
rows would be
emailvalue, answer1, answer2, etc

ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-22 : 19:03:01

can you provide some sample data and the result that you want ?

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-06-23 : 13:27:52

here are some data inserts and what I'm looking for in output is
columns would be:
email, Question-1, Question-2, Question-3, Question-4,Question-5, ...

rows would be:
user@datacom.net.com, yes, no, 1, 2, 10.00, ...

INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,1,1,'Question-1')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,2,2,'Question-2')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,3,3,'Question-3')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,4,4,'Question-4')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,5,5,'Question-5')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,6,6,'Question-6')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,7,7,'Question-7')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,8,8,'Question-8')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,9,9,'Question-9')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,10,10,'Question-10')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,11,11,'Question-11')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,12,12,'Question-12')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,13,13,'Question-13')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,14,14,'Question-14')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,15,15,'Question-15')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,16,16,'Question-16')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,17,17,'Question-17')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,18,18,'Question-18')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,19,19,'Question-19')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,20,20,'Question-20')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,21,21,'Question-21')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,22,22,'Question-22')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,23,23,'Question-23')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,24,24,'Question-24')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,25,25,'Question-25')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,26,26,'Question-26')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,27,27,'Question-27')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,28,28,'Question-28')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,29,29,'Question-29')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,30,30,'Question-30')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,31,31,'Question-31')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,32,32,'Question-32')
INSERT INTO [surveyQuestion] ([surveyId],[sortOrder],[questionId],[question])VALUES(1,33,33,'Question-33')
----------------------------------------------------------------------------------------------------------
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(1,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(2,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(3,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(4,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(5,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(6,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(7,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(10,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(12,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(16,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(17,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(8,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(9,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(11,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(13,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(14,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(15,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(18,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
INSERT INTO [surveySubmission] ([submissionId],[surveyId],[email],[submissionTimestamp])VALUES(19,1,'user@datacom.net.com','Jun 23 2006 10:22:20:417AM')
----------------------------------------------------------------------------------------------------------
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(7,2,'Under $10')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(7,1,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(7,33,'')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(7,32,'')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(7,31,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(7,30,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(7,26,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(7,25,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(7,24,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(7,23,'')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(8,1,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(9,2,'$10 to $20')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(10,7,'5')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(10,6,'4')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(10,5,'3')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(10,4,'2')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(10,3,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(12,15,'checked')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(12,14,'2')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(12,13,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(12,26,'3')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(12,25,'2')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(12,24,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(12,23,'other')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(14,17,'checked')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(11,12,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(11,11,'4')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(11,10,'3')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(11,9,'2')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(11,8,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(18,21,'checked')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(13,16,'checked')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(13,33,'something')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(13,32,'pro events')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(13,31,'5')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(13,29,'3')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(13,30,'4')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(13,28,'2')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(13,27,'1')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(17,20,'checked')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(19,22,'checked')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(15,18,'checked')
INSERT INTO [surveyAnswer] ([submissionId],[questionId],[answer])VALUES(16,19,'checked')

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-06-24 : 07:11:24

try this one from robvolk: http://sqlteam.com/item.asp?ItemID=2955

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-24 : 10:36:49

or try something like this

select	email, a.submissionId,
	max(case when q.questionId = 1 then answer end) as [Question-1],
	max(case when q.questionId = 2 then answer end) as [Question-2],
	max(case when q.questionId = 3 then answer end) as [Question-3],
	max(case when q.questionId = 4 then answer end) as [Question-4],
	max(case when q.questionId = 5 then answer end) as [Question-5]
from	surveyQuestion q inner join surveySubmission s
	on	q.surveyId	= s.surveyId
	inner join surveyAnswer a
	on	s.submissionId	= a.submissionId
group by email, a.submissionId

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-06-26 : 11:40:29

looks like its repeating 1 answer throughout.. 'checked' when it should be displaying the correct answer. meaning some answers are 'checked', but there are other answers, 1, 3,4, $10-$20, etc..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-26 : 11:48:02

then change from INNER JOIN to LEFT JOIN

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-06-26 : 12:04:19

KH, I'm not understanding your suggestion? INNER JOIN to LEFT JOIN? How is that going to prevent the 'checked' answer from repeating?
I am getting all the questions, but the answer is repeating?.. sorry :(

from surveyQuestion q left join surveySubmission s
on q.surveyId = s.surveyId
inner join surveyAnswer a
on s.submissionId = a.submissionId
where surveyId=1
group by email, a.submissionId

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-26 : 18:53:33

Missed out an condition on the join. Try this :

select	email, a.submissionId,
	max(case when q.questionId = 1 then answer end) as [Question-1],
	max(case when q.questionId = 2 then answer end) as [Question-2],
	max(case when q.questionId = 3 then answer end) as [Question-3],
	max(case when q.questionId = 4 then answer end) as [Question-4],
	max(case when q.questionId = 5 then answer end) as [Question-5]
from	surveyQuestion q inner join surveySubmission s
	on	q.surveyId	= s.surveyId
	inner join surveyAnswer a
	on	s.submissionId	= a.submissionId
	and	q.questionId	= a.questionId
group by email, a.submissionId

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-06-27 : 16:32:10

Thank you very much khan, works great.
email submissionId Question-1 Question-2 Question-3 Question-4 Question-5
-------------------------------------------------- ------------ ---------- ---------- ---------- ---------- ----------
user@datacom.net.com 7 1 NULL NULL NULL NULL
user@datacom.net.com 10 NULL NULL 1 2 3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources