Trying to combine multiple rows from a table into one row

Question

I've been trying to figure this out for a little bit now and i keep running into dead ends. Maybe someone here can help me. I work for a company and we are going to be receiving a file for units we are going to repair. In certain situations, we receive one claim for repair that has multiple units contained within it. I only have one field to import the serial number and I need to combine how ever many rows there is for a specific claim.

For example the file I would get would look like:

ClaimNumber       SerialNumber
555                12345
555                123456
555                1234567
556                4321
557                3421
558                9876
558                98765

So i need to export this table combining the serial numbers into one field like:

ClaimNumner       SerialNumber
555                12345, 123456, 1234567
556                4321
557                3421
558                9876, 98765

I am limited on receiving the file in this format, so this is all i have to work with. There are other fields in the file but i think these are the ones that I should be looking at. let me know if anyone has any ideas. Thanks!!

possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Taryn, Oct 10 '13 at 14:03
What is the real delimiter between the columns in the file? Is it a comma, a tab, 8 spaces, can I make any other guesses? — Aaron Bertrand, Oct 10 '13 at 14:04
So is your problem in translating the data, or is your problem also getting the file into the database in the first place? — Aaron Bertrand, Oct 10 '13 at 14:16
Why are you not creating a new normalized table for the serial number? What you are asking to do sounds like it's going to limit the use of that information going forward... — Daniel E., Oct 10 '13 at 14:20
I can get the file into the database no problem and test different queries on it and everything. Just cant seem to get it into the format that i need. I tried using string concatenation but since my query returns more than one serial number it always errors out. — user2867287, Oct 10 '13 at 14:20
@user2867287 then did you look at [the duplicate](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server), which has working examples, instead of trying off-the-cuff stuff on your own? — Aaron Bertrand, Oct 10 '13 at 14:21
@user2867287 See either the referenced dup above or -- http://stackoverflow.com/questions/6344950/sql-query-to-get-aggregated-result-in-comma-seperators-along-with-group-by-colum - this has been asked many times — Taryn, Oct 10 '13 at 14:23

score 1 · Accepted Answer · answered Oct 10 '13 at 14:15

Given that your table is named Claim, then I think this should do it, (not tested)

Select Main.ClaimNumber,
   Left(Main.Serials,Len(Main.Serials)-1) As "Serials" From(Select distinct T2.ClaimNumber, 
       (Select T1.SerialNumber + ',' AS [text()]
        From Claim T1
        Where T1.ClaimNumber = T2.ClaimNumber
        ORDER BY T1.ClaimNumber
        For XML PATH ('')) [Serials]
 From Claim T2) [Main]

Trying to combine multiple rows from a table into one row

1 Answers1