Solved

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

Posted on 2013-05-30
2
333 Views
Last Modified: 2013-05-30
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
0
Comment
Question by:jxharding
2 Comments
 
LVL 7

Accepted Solution

by:
Ross Turner earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:jxharding
Comment Utility
Brilliant!! thank you!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now