Solved

SQL Summarise entires into one line

Posted on 2014-12-15
3
72 Views
Last Modified: 2014-12-15
Hi All,

I have a table with ID and Invoice_Number. Multiple invoices per ID. I want to group by ID and have Invoice_Numbers as a comma seperated line against the ID.

So

1    101
1    102
1    103
2    201
2    202

becomes

1    101, 102, 103
2    201, 202

Can someone point me in the rough direction?

Thanks
0
Comment
Question by:James Elliott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40499882
Use FOR XML PATH(). E.g.

DECLARE @Sample TABLE ( ID INT, Payload INT )
 
INSERT  INTO @Sample
VALUES  ( 1, 101 ),
        ( 1, 102 ),
        ( 1, 103 ),
        ( 2, 201 ),
        ( 2, 202 );


SELECT  O.ID ,
        STUFF(( SELECT  ', ' + CAST(I.Payload AS VARCHAR(255))
                FROM    @Sample I
                WHERE   I.ID = O.ID 
				FOR XML PATH('')
              ), 1, 2, '')
FROM    @Sample O
GROUP BY O.ID;

Open in new window

0
 
LVL 12

Author Comment

by:James Elliott
ID: 40499907
Great, nearly there.

It is however cutting off the very first digit of the concatenated column.

So instead I'm getting:

1   abc
1   123
2   123
2   abc


1   bc, 123
2   23, abc
0
 
LVL 12

Author Comment

by:James Elliott
ID: 40499909
Ignore, fixed.

Thanks for your help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Resume 5 46
T-SQL: Stored Procedure Syntax 3 34
How to trim a value in SQL 2 28
sql server major issue  need help 2 51
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

726 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