A Pivot, but not exactly.

  • 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.

  • 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;

  • Interesting. Thanks!

  • 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

  • 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