Return multiple rows into one row (comma seperated) using COALESCE

Question

Post reply

Return multiple rows into one row (comma seperated) using COALESCE

Pvm_25

Ten Centuries

Points: 1073
More actions
October 8, 2009 at 8:14 pm

#205431

I know this is kinda old problem and i have tried my self and also searched many forums but couldn't solve it. Here is my problem. I have two tables. One has two columns (of my concern)
ProductID | ProductKey
123456 12
213432 23
213342 25
and second table has
ProductKey | Items
12 Printer
23 Stapler
23 Printer
25 Remote
25 Laptop
columns. 1 product can have one or more Items associated with it. and I want final output should look like
ProductID | Items
123456 Printer
213432 Stapler, Printer
213342 Remote, Laptop
I know I can return comma separated string using COALESCE. But that is only working for one ProductID. I want to store the final result set in temp table or table variable because i have to join few more table with that resultset. Code i have tried is as following.
DECLARE @ItemList VARCHAR(50), @ProductID VARCHAR (30)
SELECT @ItemList = COALESCE(@ItemList + ', ', '') +
CAST(COHORT_GROUP_DESC AS varchar(250)), @ProductID = t1.ProductID
FROM TABLE1 as T1
INNER JOIN TABLE2 AS T2
ON T1.PRODUCTKEY = T2.PRODUCTKEY
and then i was trying insert into table variable. I have tried cursors also but it didn't work. So any suggestions?......

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

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Here's an article that touches on using FOR XML PATH to do this type of concatenation.

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

If you need some more help with writing the code, please post sample data in a format we can easily use, as described by the article in my signature.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 2

You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the link in my signature.

This is what it should look like:

IF OBJECT_ID('tempdb..#TEMP1') IS NOT NULL DROP TABLE #TEMP1

IF OBJECT_ID('tempdb..#TEMP2') IS NOT NULL DROP TABLE #TEMP2

CREATE TABLE #TEMP1 (ProductID INT, ProductKey INT)

INSERT INTO #TEMP1

select 123456, 12 UNION ALL

select 213432, 23 UNION ALL

select 213342, 25

CREATE TABLE #TEMP2 (ProductKey INT, Items varchar(50))

INSERT INTO #TEMP2

select 12, 'Printer' UNION ALL

select 23, 'Stapler' UNION ALL

select 23, 'Printer' UNION ALL

select 25, 'Remote' UNION ALL

select 25, 'Laptop'

Now, here's one way to accomplish what you're looking for:

select ProductID,

Items = stuff((select ',' + Items from #TEMP2 t2 where t2.ProductKey = t1.ProductKey FOR XML PATH('')),1,1,'')

from #Temp1 t1

drop table #TEMP1

drop table #TEMP2

For the sample data you provided, it returns:

ProductIDItems

123456Printer

213432Stapler,Printer

213342Remote,Laptop

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Pvm_25 Ten Centuries Points: 1073 More actions · Answer 3

Thank you Garadin and WayneS, that article is awesome. I tried couple of methods from it and FOR XML PATH worked for me. 🙂

and sorry about partial information. I am new in posting the question. From next time I'll keep your tips/suggestions, for how to post the question, in my mind.

WayneS SSC Guru Points: 95460 More actions · Answer 4

Well, with 100+ points accumulated, you're not that new. But I'm glad that you will now be able to help us in the future... it really makes it sooooo much easier for us when we can just copy/paste your code into SSMS, and have a realistic condition on which to give you examples for how to solve your problem. There are many people on this web site that just plain don't help you if you don't do this to help them.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2