Home Forums SQL Server 2008 T-SQL (SS2K8) How to Convert Semi colon Separated Values into Column RE: How to Convert Semi colon Separated Values into Column

  • 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