Server: Msg 245, Level 16, State 1, Line 1

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)

Server: Msg 245, Level 16, State 1, Line 1

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2007-09-03 : 21:49:09

Hello All,

I have a task to convert a vertical report into horizontal report. Below is an example of the vertical table. Also the query I used to try to report it horizontally. The problem is that I’m getting an error with the query. The error is show below. Any advice would be appreciated. Thanks.

EXAMPLE TABLE:

VERTICAL
ID AMOUNT COLOR
311 210 YELLOW
311 7 BLACK
311 30 GRAY
311 8 RED
311 30 BLUE
311 240 ORANGE
311 30 WHITE
311 30 GREEN
321 30 GRAY
321 10 BLUE
321 60 YELLOW
321 60 RED
321 30 WHITE

Query:

SELECT ID,
[Yellow]= ISNULL((SELECT LENGTH FROM Test_Table WHERE COLORS = 1 AND ID = T.ID),0),
[Green] = ISNULL((SELECT LENGTH FROM Test_Table WHERE COLORS = 2 AND ID = T.ID),0),
[Blue]= ISNULL((SELECT LENGTH FROM Test_Table WHERE COLORS = 3 AND ID = T.ID),0),
[Black]= ISNULL((SELECT LENGTH FROM Test_Table WHERE COLORS = 4 AND ID = T.ID),0),
[Red]= ISNULL((SELECT LENGTH FROM Test_Table WHERE COLORS = 5 AND ID = T.ID),0),
[White] = ISNULL((SELECT LENGTH FROM Test_Table WHERE COLORS = 6 AND ID = T.ID),0)
FROM Test_Table T
GROUP BY ID, COLORS

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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-03 : 22:01:30

What's data type of LENGTH? May try with case instead.

pras2007
Posting Yak Master

216 Posts

Posted - 2007-09-03 : 22:44:07

quote:
Originally posted by rmiao

What's data type of LENGTH? May try with case instead.

Thanks Rmiao, for the quick response. The data type for Length is INT. I tried changing the lenght data type to NVARCHAR still did not work. Please advice.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-03 : 23:16:17

Take look at cross-tab report or pivot (sql2k5 only) in books online.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-04 : 16:52:50

your query is mangled.
you're grouping into colors but no colors column specified in your select (it's inside your subquery)

colors is of string type and your comparison is int

do you want to know how many colors are there for each id?

from your example, one id cannot have 2 yellows etc? so not sure what is your goal...

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources