Concatenate multiple rows to one string

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 2008 Forums

Transact-SQL (2008)

Concatenate multiple rows to one string

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2015-04-06 : 05:22:32

Hi,

I had the following query:

SELECT Customer_ID, Amount, Receipt_ID FROM
TRANSACTIONS.RT_Receipt_Detail
WHERE Customer_ID = 'YA123'

The output is:
Customer_ID Amount Receipt_ID
YA123 15.00 RYA15-1
YA123 20.00 RYA15-2
YA123 30.00 RYA15-3

How can I concatenate my receipt_ID into one string?

Customer_ID Amount Receipt_ID
YA123 15.00 RYA15-1,RYA15-2,RYA15-3
YA123 20.00 RYA15-1,RYA15-2,RYA15-3
YA123 30.00 RYA15-1,RYA15-2,RYA15-3

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-06 : 08:26:20

[code]SELECT
a.Customer_ID,
a.Amount,
STUFF(b.col1,1,1,'') AS Receipt_ID
FROM
TRANSACTIONS.RT_Receipt_Detail a
CROSS APPLY
(
SELECT
',' + b.Receipt_ID
FROM
TRANSACTIONS.RT_Receipt_Detail b
WHERE
a.Customer_ID = b.Customer_ID
ORDER BY
Receipt_ID
FOR XML PATH('')
) b(col1);[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources