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.
| Author |
Topic |
|
gamaz
Posting Yak Master
104 Posts |
 Posted - 2010-06-24 : 16:27:52
|
| Hi,This is a simplified version of a table I have posted earlier.cust_num slsman site sales_ytdc000200 904 11199 1023.1c000200 904 11121 2000.12c000201 927 11199 325.12c000201 927 11121 245.5The output of the select statement should be like the following:cust_num slsman 11199_sales_ytd 11121_sales_ytdc000200 904 1023.1 2000.12c000201 927 325.12 245.5I am formulating the following query to solve the aboveselect 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_ytdfrom prototype1 group by cust_num, slsmanHowever the above is not compiling with an error message:Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect syntax near '11199'.I am not sure where the problem is, I appreciate any help. Thanks |
|
|
jorge_martin_hn
Starting Member
2 Posts |
Posted - 2010-06-24 : 16:46:34
|
| select id, columnx1 ,(select sum(sales) from tablax where id= tablax1.id and column11199 = '11199' ) as SAles,(select sum(cost) from tablax where id= tablax1.id and column11121 = '11121' ) as Costofrom tablax as tablax1group by id, colunx1-----------------result:id , columnx1 sales, costc000200 904 1023.1 2000.12 c000201 927 325.12 245.5 |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-24 : 16:48:53
|
Column names (and also aliases) can't begin with digits.In this case use [11199_sales_ytd] instead.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
gamaz
Posting Yak Master
104 Posts |
Posted - 2010-06-24 : 16:55:07
|
| Thanks jorge and webfred for your help. I appreciate it. Webfred that was something I overlooked.Once as per your advise I put the [] s the query worked fine. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-24 : 16:56:59
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
gamaz
Posting Yak Master
104 Posts |
Posted - 2010-06-24 : 17:21:34
|
| OK,This is another question for both George and Webfred.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. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Problem with case statement