Problem with forumulating case statement

Question

Post reply

Problem with forumulating case statement

gamaz

Valued Member

Points: 59
More actions
June 24, 2010 at 2:50 pm

#220529

Hi,
I have the following sample table.
cust_num slsman site sales_ytd
c000200 904 11199 1023.1
c000200 904 11121 2000.12
c000201 927 11199 325.12
c000201 927 11121 245.5
The output of the select statement should be like the following:
cust_num slsman 11199_sales_ytd 11121_sales_ytd
c000200 904 1023.1 2000.12
c000201 927 325.12 245.5
I am formulating the following query to solve the above
select cust_num,slsman,
SUM(CASE site when '11199' THEN sales_ytd else 0 end) as 11199_sales_ytd,
SUM(CASE site when '11121' THEN sales_ytd else 0 end) as 11121_sales_ytd
from prototype1
group by cust_num, slsman
However the above is not compiling with an error message:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '11199'.
I am not sure where the problem is, I appreciate any help. Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

wierd i got the same error, not sure if it is because of the reserved word SITE or not;

this passes the syntax check:

select cust_num,slsman,

SUM(CASE WHEN [site] = '11199' THEN sales_ytd else 0 end) as [11199_sales_ytd],

SUM(CASE WHEN [site] = '11121' THEN sales_ytd else 0 end) as [11121_sales_ytd]

from prototype1

group by cust_num, slsman

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gamaz Valued Member Points: 59 More actions · Answer 2

Thanks Lowell. This worked perfect. The site seems to be not an issue.

Regards.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 3

Lowell (6/24/2010)
wierd i got the same error, not sure if it is because of the reserved word SITE or not;
this passes the syntax check:
select cust_num,slsman,
SUM(CASE WHEN [site] = '11199' THEN sales_ytd else 0 end) as [11199_sales_ytd],
SUM(CASE WHEN [site] = '11121' THEN sales_ytd else 0 end) as [11121_sales_ytd]
from prototype1
group by cust_num, slsman

Actually, the problem seems to be with the alias name ('11199_sales_ytd'). The brackets let it go through, or you can change the column name to something that starts with a character (e.g., s_11199_sales_ytd). Apparently, the digit as the first character confuses it.

Rob Schripsema
Propack, Inc.

gamaz Valued Member Points: 59 More actions · Answer 4

gamaz

Valued Member

Points: 59

June 24, 2010 at 3:09 pm

#1184723

Thanks Rob for the clarification.

Regards.

gamaz Valued Member Points: 59 More actions · Answer 5

OK,

This is another question for Rob and Lowell.

The select statement currently looks like this:

select cust_num,slsman,

SUM(CASE WHEN [site] = '11199' THEN sales_ytd else 0 end) as [11199_sales_ytd],

SUM(CASE WHEN [site] = '11121' THEN sales_ytd else 0 end) as [11121_sales_ytd]

from prototype1

group by cust_num, slsman

However I have to dynamically create this above statement. This means the site 11199 and the alias [11199_sales_ytd] need to be created by plucking the site from the corresponding row instead of hard coding it.

Same is the case for the second row. Any ideas if and how it can be achieved. In the main table I have twenty five sites. With another channel there are another twenty different sites. Thanks.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 6

If that's what you need, you'll need to use dynamic SQL. You can create a dynamic PIVOT statement along the lines of what is in this article:

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

You can do something similar creating a cross tab query (which is more similar to what you've used in your example), but again, you'll have to dynamically generate the SQL and then use EXECUTE (or sp_execute) to run it.

Rob Schripsema
Propack, Inc.