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 : 18:41:57
|
| Hi,I have the following sample table.cust_num slsman site sales_ytdc000200 904 11199 1023.1c000200 904 11121 2000.12c000201 927 11199 325.12c000201 927 11121 245.5Also I have the following site table prototypeSite1119911121The 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.5 This has been achieved through hardcoding the following.The results are exactly as I would like aboveselect 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 would like to utilize the site table to build the case statement.Here I would like to use a loop to utilize the table values in the case statementThat way I do not have to hard code the '11199' and [11100_sales_ytd] aliases.Thus if I have twenty file sites the the case statement should pick the sites in the expression instead of hardcoding.Any idea is appreciated. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
|
|
gamaz
Posting Yak Master
104 Posts |
Posted - 2010-06-25 : 12:05:08
|
| Thanks Lumbago. |
 |
|
|
|
|
|
looping through a table value to build a case stat