October 8, 2009 at 8:14 pm
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?......
October 8, 2009 at 8:51 pm
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.
October 8, 2009 at 8:58 pm
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:
ProductID Items
123456 Printer
213432 Stapler,Printer
213342 Remote,Laptop
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 9, 2009 at 7:29 am
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.
October 9, 2009 at 5:47 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply