SQL Server Performance

CSV to Columns

Discussion in 'General Developer Questions' started by vimalpercy, Sep 15, 2010.

  1. vimalpercy New Member

    Hello Experts
    I have a table like this
    ID CommaSeperatedString
    1 a,b,c,d,e
    2 x,y,z
    What I want is to convert this into
    ID Text
    1 a
    1 b
    1 c
    2 x
    2 y
    like this.
    Is it possible?
  2. FrankKalis Moderator

    There are quite a few split functions out there. Basicall they all do something similar to
    SELECT
    T.id, RIGHT(LEFT(T.csv,Number-1),
    CHARINDEX(',',REVERSE(LEFT(','+T.csv,Number-1))))
    FROM
    master..spt_values,
    your_table T
    WHERE
    Type = 'P' AND Number BETWEEN 1 AND LEN(T.csv)+1
    AND
    (SUBSTRING(T.csv,Number,1) = ',' OR SUBSTRING(T.csv,Number,1) = '')

  3. vimalpercy New Member

    Vielen Dank Frank Kalis.
    This is exactly what I was looking for.
    Really appreciated
    Have a nice day!
  4. vimalpercy New Member

    Hello Frank Kalis
    Say I have a UDF function called SplitCSVToTable which returns a table of splited items.
    I have another table
    TableA --> ID CSVString
    1 a,b,c,d
    2 x,y,z
    How can I use it in a select statement like Select ID ,dbo.SplitCSVToTable(CSVString) From TableA
  5. Adriaan New Member

    You would put the UDF in the FROM clause, with an alias, like this:

    SELECT f.* FROM dbo.SplitCSVToTable(CSVString) f
    ... and treat it like you would treat any (read-only) table.
  6. FrankKalis Moderator

    The way you describe it, you're looking now for a scalar function, but that won't work when you want to return the split strings as a table. What should your result set look like?
  7. satya Moderator

  8. vimalpercy New Member

    Thank you Satya,Adriaan and Frankkalis for the information. Sorry for not following up on this, was having a short vacation.
    The format I am looking for is same as my first post.
    SplitCSV function return a table and it works fine when you process one item. But when you want to pass the value for the input parameter of the function and try to treat the return value of the SPLITCSV as a subtable it breaks.
    ID CommaSeperatedString
    1 a,b,c,d,e
    2 x,y,z
    ID and CommaSeperatedString will be my paramter to the splitCSV function and that will return a table with rows as
    ID Text
    1 a
    1 b
    1 c
    1 d
    1 e
    and I should be able to link the ID of main table and result from UDF using the ID.
    I tried the method Aadrian has mentioned but not able to pass the CSVString as a column of a table.

Share This Page