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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy