I need some serious help

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 QuestionID 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 zipOn Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>Out of the 3 tables below. I need to get a tabular report like this
(snip)
Hi Dave,
You posted this same message about a week ago in .programming. David
Portas gave you an excellent answer. For your convenience, I've copied the
complete answer below. Do read the entire message and follow the link in
the last paragraph!
(start quote)
What is the rationale for your table design and for persisting the UI
information in the database? Don't you have normalized tables to
represent this information? If this is some kind of content management
layer then I would suggest you don't use it for reporting. Utilize it
as a Staging database with an ETL process to load into a normalized
data model. There are plenty of reasons why the
"entity-attribute-value" model you are proposing should be avoided in
SQL.
Anyway, take a look here for some solutions to your cross-tab report:
http://www.aspfaq.com/2462
(end quote)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||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 Q1,
SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
FROM FormCreator_Results
GROUP BY SurveyResultID
GO
--
David Fetrow
Helixpoint LLC.
http://www.helixpoint.com
davef@.helixpoint.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
> >Out of the 3 tables below. I need to get a tabular report like this
> (snip)
> Hi Dave,
> You posted this same message about a week ago in .programming. David
> Portas gave you an excellent answer. For your convenience, I've copied the
> complete answer below. Do read the entire message and follow the link in
> the last paragraph!
> (start quote)
> What is the rationale for your table design and for persisting the UI
> information in the database? Don't you have normalized tables to
> represent this information? If this is some kind of content management
> layer then I would suggest you don't use it for reporting. Utilize it
> as a Staging database with an ETL process to load into a normalized
> data model. There are plenty of reasons why the
> "entity-attribute-value" model you are proposing should be avoided in
> SQL.
> Anyway, take a look here for some solutions to your cross-tab report:
> http://www.aspfaq.com/2462
> (end quote)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||The result of a CASE need to be of the same datatype. Is the different expressions inside the CASE
returns different datatypes, then SQL Server will try implicit datatype conversion so that end
result is of the datatype which has the highest precedence according to "Datatype Precedence" in
Books Online. Int is higher than the string datatypes, and the string 'David' cannot be converted to
an int. What you can do is, inside the CASE, use an explicit CAST around the columns which are
integer so you convert them to appropriate strings.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.gbl...
> 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 Q1,
> SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
> SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
> SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
> FROM FormCreator_Results
> GROUP BY SurveyResultID
> GO
> --
>
> David Fetrow
> Helixpoint LLC.
> http://www.helixpoint.com
> davef@.helixpoint.com
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
>> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>> >Out of the 3 tables below. I need to get a tabular report like this
>> (snip)
>> Hi Dave,
>> You posted this same message about a week ago in .programming. David
>> Portas gave you an excellent answer. For your convenience, I've copied the
>> complete answer below. Do read the entire message and follow the link in
>> the last paragraph!
>> (start quote)
>> What is the rationale for your table design and for persisting the UI
>> information in the database? Don't you have normalized tables to
>> represent this information? If this is some kind of content management
>> layer then I would suggest you don't use it for reporting. Utilize it
>> as a Staging database with an ETL process to load into a normalized
>> data model. There are plenty of reasons why the
>> "entity-attribute-value" model you are proposing should be avoided in
>> SQL.
>> Anyway, take a look here for some solutions to your cross-tab report:
>> http://www.aspfaq.com/2462
>> (end quote)
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||It looks like your OptionTextBoxValue column must be a VARCHAR yet you
are trying to SUM it! I'll guess you only want to sum the numeric
values. Add a WHERE clause:
...
WHERE QuestionID BETWEEN 18 AND 21
or
...
WHERE OptionTextboxValue NOT LIKE '%[^0-9]%'
This complexity is one of the consequences of a having weakly-typed,
multi-valued columns and is an example of why your table design badly
needs fixing.
--
David Portas
SQL Server MVP
--|||Oops. See David's post. I missed the fact that SUM is performed, and it is pretty darn hard to sum
strings. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:%238OmS7OFFHA.1296@.TK2MSFTNGP10.phx.gbl...
> The result of a CASE need to be of the same datatype. Is the different expressions inside the CASE
> returns different datatypes, then SQL Server will try implicit datatype conversion so that end
> result is of the datatype which has the highest precedence according to "Datatype Precedence" in
> Books Online. Int is higher than the string datatypes, and the string 'David' cannot be converted
> to an int. What you can do is, inside the CASE, use an explicit CAST around the columns which are
> integer so you convert them to appropriate strings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.gbl...
>> 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 Q1,
>> SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
>> SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
>> SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
>> FROM FormCreator_Results
>> GROUP BY SurveyResultID
>> GO
>> --
>>
>> David Fetrow
>> Helixpoint LLC.
>> http://www.helixpoint.com
>> davef@.helixpoint.com
>> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>> news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
>> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>> >Out of the 3 tables below. I need to get a tabular report like this
>> (snip)
>> Hi Dave,
>> You posted this same message about a week ago in .programming. David
>> Portas gave you an excellent answer. For your convenience, I've copied the
>> complete answer below. Do read the entire message and follow the link in
>> the last paragraph!
>> (start quote)
>> What is the rationale for your table design and for persisting the UI
>> information in the database? Don't you have normalized tables to
>> represent this information? If this is some kind of content management
>> layer then I would suggest you don't use it for reporting. Utilize it
>> as a Staging database with an ETL process to load into a normalized
>> data model. There are plenty of reasons why the
>> "entity-attribute-value" model you are proposing should be avoided in
>> SQL.
>> Anyway, take a look here for some solutions to your cross-tab report:
>> http://www.aspfaq.com/2462
>> (end quote)
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>>
>