May 26, 2015 at 9:51 pm
i Have a scenario like this
There is a source table containing 2 columns Col1 and Col2 with data as follows:
Col1 Col2
—— ——
a l
b p
a m
a n
b q
x y
I need to load target table with following values from the above mentioned source:
Col1 Col2
—— ——
a l, m, n
b p, q
x y
any ideas and suggestions ?
May 26, 2015 at 10:06 pm
May just be my phone but the formating of what you say you have plus what you want, would appear to not be consistent (you use commas, or not) and doesn't appear to match the number of columns (ie 2 commas indicating 3 fields but with only 2 columns )
Steve.
May 26, 2015 at 10:10 pm
output should be like this
col1 col2
a l,m,n
b P,q
x y
May 27, 2015 at 1:16 am
You can use below query -
create table #tmp1
(col1 varchar(1)
,col2 varchar(1)
)
insert into #tmp1
select 'a','l' UNION ALL
select 'b','p' UNION ALL
select 'a','m' UNION ALL
select 'a','n' UNION ALL
select 'b','q' UNION ALL
select 'x','y'
SELECT col1
,STUFF((SELECT ', ' + CAST(col2 AS VARCHAR(10)) [text()]
FROM #tmp1
WHERE col1 = t.col1
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM #tmp1 t
GROUP BY col1
____________________________________________________________
APViewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply