May 2, 2013 at 9:43 am
I have a table that could look like the following:
ID FK_ID Value
1 100 'Blue'
2 100 'Black'
3 100 'Green'
4 101 'Blue'
5 101 'Green'
6 102 'Black'
I need a query that outputs the following:
FK_ID NewVal
100 'Blue,Black,Green'
101 'Blue, Green'
102 'Black'
Also, I won't always know that 'Blue', 'Black', or 'Green' are going to be the distinct values.
May 2, 2013 at 9:56 am
Use something like this:
with basedata as (
select distinct
FK_ID
from
dbo.MyTable
)
select
FK_ID,
NewVal = stuff((select ',' + Value
from dbo.MyTable mt
where mt.FK_ID = bd.FK_ID
order by mt.ID
for xml path(''),TYPE).value('.','varchar(max)'),1,1,'')
from
basedata bd;
May 2, 2013 at 9:59 am
Interesting. Thanks!
May 2, 2013 at 3:36 pm
the method above is useful since its dynamic, but i wanted to show you a more manual way of doing it as well.
CREATE taBLE #TEMP
(
id INT,
fk_id INT,
value VARCHAR(10)
)
INSERT INTO #TEMP
VALUES(1, 100, 'Blue'),
(2, 100, 'Black'),
(3, 100, 'Green'),
(4, 101, 'Blue'),
(5, 101, 'Green'),
(6, 102, 'Black')
SELECT FK_ID, REPLACE(REPLACE(RTRIM(LTRIM(COALESCE(Blue ,'') +' '+ COALESCE(Black,'') +' '+ COALESCE(Green ,''))),' ',','),',,',',')AS VALUESS
FROM(
select fk_id, MIN(case when value = 'Blue' then 'Blue' END) Blue,MIN(case when value = 'Black' then 'Black' END) black, MIN(case when value = 'Green' then 'Green' END)Green
from #TEMP
group by fk_id)I
May 2, 2013 at 3:41 pm
Thanks, but like I mentioned - "I won't always know that 'Blue', 'Black', or 'Green' are going to be the distinct values."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply