Split Comma Separated Values to Column

  • yogi123

    SSCrazy

    Points: 2761

    Hi Guys,

    I have one task and i need to build the logic to split the columns by comma,

    see below for table definition

    CREATE TABLE #X1
    (SCHID VARCHAR(250))

    INSERT INTO #X1 VALUES ('101,102,103,104,105,106,107')

    The desired output should be,

    ID	AdditionalIds
    101 102|103|104|105|106|107

    Can you please help me to build this? I appropriate for your help, and Thanks for your help.

     

  • Andrey

    Mr or Mrs. 500

    Points: 554

  • Phil Parkin

    SSC Guru

    Points: 243477

    So the number of columns returned = (number 0f commas + 1), is that correct?

    What happens if the number of commas varies by input data row?

    What should the columns be called?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • yogi123

    SSCrazy

    Points: 2761

    Thanks for your reply,

     

    The First Column need to have only one Value before comma, and rest it need to go to other column.

  • Thom A

    SSC Guru

    Points: 98212

    Seems like the easiest would be to use CHARINDEX and STUFF:

    SELECT I.ID,
    I.AdditionalIDs
    FROM (VALUES ('101,102,103,104,105,106,107')) X1(SCHID)
    CROSS APPLY (VALUES(LEFT(X1.SCHID,NULLIF(CHARINDEX(',',X1.SCHID),0)-1),STUFF(X1.SCHID,1,NULLIF(CHARINDEX(',',X1.SCHID),0),'')))I(ID,AdditionalIDs);

    You could also write the above as follows, which may be easier to read:

    SELECT I.ID,
    I.AdditionalIDs
    FROM (VALUES ('101,102,103,104,105,106,107')) X1(SCHID)
    CROSS APPLY (VALUES(NULLIF(CHARINDEX(',',X1.SCHID),0))) CI(I)
    CROSS APPLY (VALUES(LEFT(X1.SCHID,CI.I-1),STUFF(X1.SCHID,1,CI.I,'')))I(ID,AdditionalIDs);

    • This reply was modified 1 week, 3 days ago by  Thom A. Reason: Added extra version

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Andrey

    Mr or Mrs. 500

    Points: 554

    or such one:

     


    with data as
    (select '101,102,103,104,105,106,107' [SCHID])
    select
    left(SCHID,charindex(',',SCHID)-1) [Id]
    ,stuff(SCHID,1,charindex(',',SCHID),'') [AdditionalIds]
    from data
  • Andrey

    Mr or Mrs. 500

    Points: 554

    Thom A wrote:

    You could also write the above as follows, which may be easier to read

    with all due respect, such constructions are "easier to read"ย  if you deal with or create them on a regular basis ๐Ÿ˜‰

  • Phil Parkin

    SSC Guru

    Points: 243477

    Andrey wrote:

    Thom A wrote:

    You could also write the above as follows, which may be easier to read

    with all due respect, such constructions are "easier to read"ย  if you deal with or create them on a regular basis ๐Ÿ˜‰

    Which is why the qualifying words may be were used.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • yogi123

    SSCrazy

    Points: 2761

    Thanks Guys,

    It helps me to develop in my logic.

     

  • Jonathan AC Roberts

    SSCoach

    Points: 16685

    SELECT LEFT(SCHID, x.Pos - 1) [Id],
    REPLACE(SUBSTRING(SCHID, x.Pos + 1, 8000),',','|') [AdditionalIds]
    FROM #X1
    CROSS APPLY(VALUES (CHARINDEX(',', SCHID))) x(Pos)

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply