• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

SQL Summarise entires into one line

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
James Elliott
Asked:
James Elliott
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
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
 
James ElliottAuthor Commented:
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
 
James ElliottAuthor Commented:
Ignore, fixed.

Thanks for your help.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now