I need serious help - 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

SQL Server 2000 Forums

Transact-SQL (2000)

I need serious help

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-02-16 : 22:30:47

Out of the 3 tables below. I need to get a tabular report like this

First Name Last Name Email Can you receive HTML email Address State Zip
Tom Thompson xxxx@xxxxxxxx.com Yes 575 mystreet Rd AK 14525
steve Smith aaaa@aaaaaaaa.com No 575 double Dam Rd AL 13323

table 1

QuestionID ModID Question
18 362 First Name
19 362 Last Name
20 362 Email
21 362 Can you receive HTML email
23 362 Address
24 362 State
25 362 Zip

Table 2

Resultid QuestionID OptionD OptionTextboxValue SurveyResultID modid
2051 18 -55 Tom 140 362
2052 19 -55 Thompson 140 362
2053 20 -55 xxxx@xxxxxxxx.com 140 362
2055 21 47 140 362
2056 23 -55 575 mystreet Rd 140 362
2057 24 52 140 362
2058 25 -55 14525 140 362
2059 18 -55 steve 140 362
2060 19 -55 Smith 140 362
2061 20 -55 aaaa@aaaaaaaa.com 140 362
2063 21 48 140 362
2064 23 -55 575 double Dam Rd 140 362
2065 24 53 140 362
2066 25 -55 13323 140 362

Table3

optionid Que

stionID optionText
41 18 firstName
43 19 lastName
45 20 emailAdd
47 21 Yes
48 21 No
50 23 address
52 24 AK
53 24 AL
55 25 zip

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-17 : 06:34:35

read this it should help you:
http://www.sqlteam.com/item.asp?ItemID=2955

Go with the flow & have fun! Else fight the flow

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-02-17 : 08:13:44

Sorry Guys. I am new to crosstabs. I get this error:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'David' to a column of data type int.

SELECT SurveyResultID,
SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS FirstName,
SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS LastName,
SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Address,
SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS City
SUM(CASE QuestionID WHEN 22 THEN OptionTextboxValue ELSE 0 END) AS State
FROM FormCreator_Results
GROUP BY SurveyResultID
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-17 : 08:31:53

well you're OptionTextboxValue is of type varchar which you can't sum.
maybe you need to replace it with OptionID ??

Go with the flow & have fun! Else fight the flow

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-02-17 : 13:21:57

I got the following to work, but I somehow have to do a join on another table to get the state text
SELECT SurveyResultID,
MAX(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE '' END) AS FirstName,
MAX(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE '' END) AS LastName,
MAX(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE '' END) AS Address,
MAX(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE '' END) AS City,
MAX(CASE QuestionID WHEN 22 THEN OptionTextboxValue ELSE '' END) AS State
FROM FormCreator_Results
GROUP BY SurveyResultID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources