Combine multiple rows into one column

Question

Post reply

Combine multiple rows into one column

Jako de Wet

Hall of Fame

Points: 3123
More actions
November 15, 2011 at 12:44 am

#246843

Hi, Im pretty sure my header doesn't explain what I need 🙂
I have a xls sheet with values like this:
9780431016191 DHodge
9780431016771 JBailey
9780431016818 CMacro
9780431016818 KHartley
9780431016825 CMacro
9780431016825 KHartley
As you can see there are some records where the SKU duplicates for different authors, I want the result to be
9780431016191 D Hodge
9780431016771 J Bailey
9780431016818 C Macro, K Hartley
9780431016825 C Macro, K Hartley
any ideas?
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

I am sure Lowell posted the reply for similar issue recently.

@lowell: Are you here?

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Jako de Wet (11/15/2011)
Hi, Im pretty sure my header doesn't explain what I need 🙂
I have a xls sheet with values like this:
9780431016191 DHodge
9780431016771 JBailey
9780431016818 CMacro
9780431016818 KHartley
9780431016825 CMacro
9780431016825 KHartley
As you can see there are some records where the SKU duplicates for different authors, I want the result to be
9780431016191 D Hodge
9780431016771 J Bailey
9780431016818 C Macro, K Hartley
9780431016825 C Macro, K Hartley
any ideas?
Thank you

It's always better to supply your data in a readily consumable format, like this: -

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment

END

SELECT ID, NAME

INTO #testEnvironment

FROM (SELECT 9780431016191, 'D Hodge'

UNION ALL SELECT 9780431016771, 'J Bailey'

UNION ALL SELECT 9780431016818, 'C Macro'

UNION ALL SELECT 9780431016818, 'K Hartley'

UNION ALL SELECT 9780431016825, 'C Macro'

UNION ALL SELECT 9780431016825, 'K Hartley') a(ID,NAME)

OK, on to your code. Using the above table, you'd be looking at this: -

SELECT ID, STUFF((SELECT ', ' + NAME

FROM #testEnvironment r2

WHERE r2.ID = r1.ID

ORDER BY ID

FOR XML PATH('')), 1, 1, '') AS NAME

FROM #testEnvironment r1

GROUP BY ID

Which returns: -

ID NAME

--------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

9780431016191 D Hodge

9780431016771 J Bailey

9780431016818 C Macro, K Hartley

9780431016825 C Macro, K Hartley

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jako de Wet Hall of Fame Points: 3123 More actions · Answer 3

wow, that easy!

thanks for the tip, will keep that in mind, and thanks a lot for the help!

need to read up on FOR XML

ahmedmujtaba88 Old Hand Points: 349 More actions · Answer 4

Please provide an information wheather there are 3 colums in your sheet or 2 columns .