SQL server 2005, concatenate groups e.g OrderNo1 : OrderID 1,2,4.

Hi, I have a table

OrderNo  OrderID
OrderNo1 1
OrderNo1 2
OrderNo1 3
OrderNo2 4
OrderNo2 5
How in SQL 2005 will I be able to , per OrderNo, get a concatenated list of IDs? i.e.

OrderNo1  1,2,3
OrderNo2  4,5
jxhardingAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ross TurnerConnect With a Mentor Management Information Support AnalystCommented:
Hi

Try this, i created and example with the link below

http://sqlfiddle.com/#!3/ac5a9/8

select distinct 
Orderno,
STUFF(
               (SELECT      ',' + Cast(Order_ID.OrderID as varchar(30))
               FROM      test AS Order_ID
               WHERE      Order_ID.OrderNo = test.OrderNo
                    
               FOR XML PATH('')), 1, 1, '') AS OrderID
from test

Open in new window

0
 
jxhardingAuthor Commented:
Brilliant!! thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.