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 |
|
funk.phenomena
Posting Yak Master
121 Posts |
 Posted - 2012-09-24 : 14:52:45
|
| Hi All !!! I have the following Orders Table. The ORDER_ID is repeated on multiple lines depending on the amount of colours that were ordered:[CODE]ORDER_ID; PRODUCT_COLOR123456 Blue 123456 Black123456 Red123457 Blue123457 Red123458 black[/CODE]I need to have the order ID outputted on a single line, with the colors combined into one field, so the output is as follows:[CODE]123456 'Blue + Black + Red'123457 'Blue + Red'123458 'Black'[/CODE]I'm not quite sure how to do this, Any ideas? thanKS![CODE]SELECT ORDER_ID, PRODUCT_COLOR FROM ORDERS[/CODE] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-24 : 15:34:46
|
| [code]SELECT ORDER_ID,STUFF((SELECT ' + ' + PRODUCT_COLOR FROM ORDERS WHERE ORDER_ID = t.ORDER_ID FOR XML PATH('')) ,1,3,'') AS ColorsFROM (SELECT DISTINCT ORDER_ID FROM ORDERS) t[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
funk.phenomena
Posting Yak Master
121 Posts |
Posted - 2012-09-24 : 15:47:06
|
| Visakh - You've gone nuts with your SQL wizardry! This works perfect, thanks!!!!!!!!!!!!!!!!!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-24 : 16:41:49
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Simple Problem!