November 9, 2011 at 8:39 am
--the idea is i need col2 to be a delimited list of all the imagenames for the given stock numer.
create table #test
(
stock int
, imgname varchar(100)
)
DELETE FROM #test
insert into #test (stock,imgname )
SELECT 1009931,'fw_1009931_01'
UNION ALL
SELECT 1009931,'fw_1009931_02'
UNION ALL
SELECT 1009931,'fw_1009931_03'
UNION ALL
SELECT 1009931,'fw_1009931_04'
UNION ALL
SELECT 1009931,'fw_1009931_05'
UNION ALL
SELECT 1009931,'fw_1009931_06'
UNION ALL
SELECT 1009931,'fw_1009931_07'
UNION ALL
SELECT 1009931,'fw_1009931_08'
UNION ALL
SELECT 1009931,'fw_1009931_09'
UNION ALL
SELECT 1009931,'fw_1009931_10'
UNION ALL
SELECT 1009931,'fw_1009931_12'
UNION ALL
SELECT 1010153,'t_1010153_01'
UNION ALL
SELECT 1010153,'t_1010153_02'
UNION ALL
SELECT 1010153,'t_1010153_03'
UNION ALL
SELECT 1010153,'t_1010153_04'
UNION ALL
SELECT 1010153,'t_1010153_05'
UNION ALL
SELECT 1010153,'t_1010153_06'
--returns
--column1|Column2
--1009931, fw_1009931_01,fw_1009931_02,fw_1009931_03,fw_1009931_04,fw_1009931_05,fw_1009931_06,fw_1009931_07,fw_1009931_08,fw_1009931_09,fw_1009931_10,fw_1009931_11,fw_1009931_12
--1010153, t_1010153_01,t_1010153_02,t_1010153_03,t_1010153_04,t_1010153_05,t_1010153_06
--the idea is i need col2 to be a delimited list of all the imagenames for the given stock numer.
November 9, 2011 at 9:10 am
neverind i found the solution
select distinct stock
, (select imgname+','
from #test
where stock = tt.stock
order by imgname
for xml path('')
) st
from #test tt
November 9, 2011 at 9:11 am
Something like this should work for you.
SELECT stock,
Stuff((SELECT ', ' + imgname
FROM #test t2
WHERE t1.stock = t2.stock
ORDER BY t2.imgname
FOR XML PATH('')), 1, 1, ' ')
FROM #test t1
GROUP BY stock
order by stock
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 9, 2011 at 9:12 am
First, thanks for the sample data! It makes things much easier 🙂
Let's just tidy it up a little bit: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
SELECT IDENTITY(INT,1,1) AS ID,
stock, imgname
INTO #testEnvironment
FROM (SELECT 1009931,'fw_1009931_01' UNION ALL
SELECT 1009931,'fw_1009931_02' UNION ALL
SELECT 1009931,'fw_1009931_03' UNION ALL
SELECT 1009931,'fw_1009931_04' UNION ALL
SELECT 1009931,'fw_1009931_05' UNION ALL
SELECT 1009931,'fw_1009931_06' UNION ALL
SELECT 1009931,'fw_1009931_07' UNION ALL
SELECT 1009931,'fw_1009931_08' UNION ALL
SELECT 1009931,'fw_1009931_09' UNION ALL
SELECT 1009931,'fw_1009931_10' UNION ALL
SELECT 1009931,'fw_1009931_12' UNION ALL
SELECT 1010153,'t_1010153_01' UNION ALL
SELECT 1010153,'t_1010153_02' UNION ALL
SELECT 1010153,'t_1010153_03' UNION ALL
SELECT 1010153,'t_1010153_04' UNION ALL
SELECT 1010153,'t_1010153_05' UNION ALL
SELECT 1010153,'t_1010153_06') a(stock,imgname)
OK, now on to your query!! 😀
There are so many ways to concatenate a list, but I'll only show you the one I generally use.
SELECT stock AS column1, STUFF((SELECT ',' + imgname
FROM #testEnvironment r2
WHERE r2.stock = r1.stock
ORDER BY ID
FOR XML PATH('')), 1, 1, '') AS Column2
FROM #testEnvironment r1
GROUP BY stock
This returns: -
column1 Column2
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1009931 fw_1009931_01,fw_1009931_02,fw_1009931_03,fw_1009931_04,fw_1009931_05,fw_1009931_06,fw_1009931_07,fw_1009931_08,fw_1009931_09,fw_1009931_10,fw_1009931_12
1010153 t_1010153_01,t_1010153_02,t_1010153_03,t_1010153_04,t_1010153_05,t_1010153_06
How does that work for you? 🙂
November 9, 2011 at 9:32 am
MY SOLUTION WAS CLOSE.
WHATS UP W/ THE stuff function u guya are using. Is that to get rid of the trailing comma? How?
November 9, 2011 at 9:34 am
lol. I see.
thanks guys
November 9, 2011 at 9:35 am
Notice the time of both posts are a few seconds after you posted. We were all typing at the same time. Using Stuff is another way of handling what you are doing. It gives you a little more flexibility and eliminates the comma at the end. You can use to do some additional formatting if you desire.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply