Home Forums SQL Server 2008 SQL Server Newbies How to join to a comma delimited list RE: How to join to a comma delimited list

  • June 16, 2014 at 3:17 pm

    #1722102

    rightontarget (6/16/2014)

    Hi all,

    I have a table:

    id pallet color

    1 a red,blue

    2 b yellow

    and function that will split colors:

    declare @colors varchar(100) = 'red,blue'

    select * from SeparateValues (@colors,',')

    will return

    red

    blue

    Question: how do i join to show:

    pallet color

    a red

    a blue

    b yellow

    I think you are looking for this:

    DECLARE @YourTable TABLE

    (

    id int primary key,

    pallet char(1) not null,

    color varchar(100) not null

    )

    INSERT @YourTable (id, pallet, color)

    VALUES(1, 'a', 'red,blue'),(2, 'b', 'yellow');

    SELECT pallet, Item

    FROM @YourTable t

    CROSS APPLY SeparateValues(color,',')

    Edit: Added SQL Code tag thingee around my solution

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001