SQL Server Performance

Split single columns into multiple rows

Discussion in 'SQL Server 2005 General DBA Questions' started by gopal, Mar 24, 2011.

  1. gopal Member

    I need to split the single column into multiple rows.Column1 Column2
    XYZ ## Err001## => Fieldname: [cbc_dt], ## Err002## => Fieldname: [mod_dt], ## Err003## =>
    Fieldname: [abg_dt], ## Err004## => Fieldname: [chg_dt], The output should be something like this.
    XYZ ## Err001## => Fieldname: [cbc_dt],
    XYZ ## Err002## => Fieldname: [mod_dt],
    XYZ ## Err003## => Fieldname: [abg_dt],
    XYZ ## Err004## => Fieldname: [chg_dt],
    The data is stored in the table and need to loop it thru the entire tables.
    Thanks in advance.
    gopal, Mar 24, 2011
    #2

  2. FrankKalis Moderator

    One possible solution would be:
    DECLARE @t TABLE (
    c1 char(3),
    c2 varchar(MAX)
    )
    INSERT INTO @t
    (c1, c2)
    VALUES
    ('XYZ', '## Err001## => Fieldname: [cbc_dt], ## Err002## => Fieldname: [mod_dt], ## Err003## => Fieldname: [abg_dt], ## Err004## => Fieldname: [chg_dt],')

    SELECT
    c1,
    RIGHT(LEFT(c2,Number-1),CHARINDEX(',',REVERSE(LEFT(', '+c2,Number-1))))
    FROM
    master.dbo.spt_values SV, @t T
    WHERE
    Number BETWEEN 1 AND LEN(c2)
    AND
    SUBSTRING(c2,Number,1) = ', ' AND SUBSTRING(c2,Number-1,1) <> ', ';
    c1
    XYZ ## Err001## => Fieldname: [cbc_dt]
    XYZ ## Err002## => Fieldname: [mod_dt]
    XYZ ## Err003## => Fieldname: [abg_dt]
    XYZ ## Err004## => Fieldname: [chg_dt]

    (4 row(s) affected)


    FrankKalis, Mar 24, 2011
    #3

Share This Page