Solved

SQL Summarise entires into one line

Posted on 2014-12-15
3
60 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
  • 2
3 Comments
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

708 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

17 Experts available now in Live!

Get 1:1 Help Now