How to Convert Semi colon Separated Values into Column

  • I am working on a integration project where I am receiving three string parameters ItemCode ItemName Amount

    and values will be in this format

    ItemCod Parameter values

    T1;T2;T3;

    ItemName Parameter values

    Pencil Box;Eraser;Mouse Pad;

    Amount Paramter values

    1900;2000;8900;

    Now I would like to have a procedure in which i would receive these parameters and the query will convert this as column and records. I am not a SQLServer guy so somebody proposed a solution but it is limited to two records only where my requirement is as many records depends on the number of semi colon separated strings. The defined structure is that all parameters will have equal number of values.

    here is the solution somebody gave it to me.

    INSERT INTO t

    (cod, name)

    VALUES

    ('T1;T2;T3;T4;T5;',

    'Pencil Box;Eraser;Board Marker;Trimmer;Ball Point;');

    SELECT

    CAST('<r>'+REPLACE(cod,';','</r><r>')+'</r>' AS XML).query('/r[1]').value('.','varchar(256)') cod,

    CAST('<r>'+REPLACE(name,';','</r><r>')+'</r>' AS XML).query('/r[1]').value('.','varchar(256)') name

    INTO #tmpTable FROM t

    insert INTO #tmpTable

    SELECT

    CAST('<r>'+REPLACE(cod,';','</r><r>')+'</r>' AS XML).query('/r[2]').value('.','varchar(256)') cod,

    CAST('<r>'+REPLACE(name,';','</r><r>')+'</r>' AS XML).query('/r[2]').value('.','varchar(256)') name

    FROM t;

    select * from #tmpTable

    Here is the helper script to create required table so it wont waste your valuable time

    CREATE TABLE [dbo].[t](

    [cod] [varchar](350) NULL,

    [name] [varchar](300) NULL

    ) ON [PRIMARY]

    GO

  • post the expected output based on your above data's example

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • You could use a recursive cte to split the strings:

    WITH rep AS

    (

    SELECT

    cod

    ,';' delim

    ,name

    from t

    UNION ALL

    SELECT

    LEFT(cod, CHARINDEX(delim, cod, 1) - 1)

    ,delim

    ,LEFT(name, CHARINDEX(delim, name, 1) - 1)

    FROM rep

    WHERE (CHARINDEX(delim, cod, 1) > 0)

    UNION ALL

    SELECT

    RIGHT(cod, LEN(cod) - CHARINDEX(delim, cod, 1))

    ,delim

    ,RIGHT(name, LEN(name) - CHARINDEX(delim, name, 1))

    FROM rep

    WHERE (CHARINDEX(delim, cod, 1) > 0)

    )

    SELECT

    cod

    ,name

    FROM rep

    WHERE (CHARINDEX(delim, cod, 1) = 0)

    AND LEN(cod)>0

    OPTION (MAXRECURSION 0);

  • DECLARE @ItemCod VARCHAR(8000) = 'T1;T2;T3;'

    DECLARE @ItemName VARCHAR(8000) = 'Pencil Box;Eraser;Mouse Pad;'

    DECLARE @Amount VARCHAR(8000) = '1900;2000;8900;'

    SELECT

    c.ItemNumber,

    ItemCod = c.Item,

    ItemName = n.Item,

    Amount = a.Item

    FROM dbo.DelimitedSplit8K(@ItemCod,';') c

    INNER JOIN dbo.DelimitedSplit8K(@ItemName,';') n ON n.ItemNumber = c.ItemNumber

    INNER JOIN dbo.DelimitedSplit8K(@Amount,';') a ON a.ItemNumber = c.ItemNumber

    WHERE c.Item <> ''

    -- Results

    ItemNumberItemCodItemNameAmount

    1 T1 Pencil Box 1900

    2 T2 Eraser 2000

    3 T3 Mouse Pad 8900

    The function DelimitedSplit8K is discussed in this article[/url].

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Life will be much easier if you pass that as an XML parameter.

    DECLARE @x XML =

    '

    <Items>

    <Item>

    <ItemCode>T1</ItemCode>

    <ItemName>Pencil Box</ItemName>

    <Amount>1900</Amount>

    </Item>

    <Item>

    <ItemCode>T2</ItemCode>

    <ItemName>Eraser</ItemName>

    <Amount>2000</Amount>

    </Item>

    <Item>

    <ItemCode>T3</ItemCode>

    <ItemName>Mouse Pad</ItemName>

    <Amount>8900</Amount>

    </Item>

    </Items>

    ';

    SELECT

    i.value('(ItemCode)[1]','varchar(50)'),

    i.value('(ItemName)[1]','varchar(50)'),

    i.value('(Amount)[1]','numeric')

    FROM

    @x.nodes('//Items/Item') TAB(i)

    https://sqlroadie.com/

  • Chris...hats off to yaaaaaaaaaaaaaaaaa πŸ™‚ so awesome and superb fast...I simply praised you for the awesome query πŸ™‚ Wish I could learn SQL like ya πŸ™‚

    ChrisM@Work (3/7/2013)


    DECLARE @ItemCod VARCHAR(8000) = 'T1;T2;T3;'

    DECLARE @ItemName VARCHAR(8000) = 'Pencil Box;Eraser;Mouse Pad;'

    DECLARE @Amount VARCHAR(8000) = '1900;2000;8900;'

    SELECT

    c.ItemNumber,

    ItemCod = c.Item,

    ItemName = n.Item,

    Amount = a.Item

    FROM dbo.DelimitedSplit8K(@ItemCod,';') c

    INNER JOIN dbo.DelimitedSplit8K(@ItemName,';') n ON n.ItemNumber = c.ItemNumber

    INNER JOIN dbo.DelimitedSplit8K(@Amount,';') a ON a.ItemNumber = c.ItemNumber

    WHERE c.Item <> ''

    -- Results

    ItemNumberItemCodItemNameAmount

    1 T1 Pencil Box 1900

    2 T2 Eraser 2000

    3 T3 Mouse Pad 8900

    The function DelimitedSplit8K is discussed in this article[/url].

  • Gosh :blush: thanks! Hang around here, read the articles...you'll soon pick it up.

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 7 posts - 1 through 6 (of 6 total)

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