Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Splitting column values iin multiple columns and assigning it to a row RE: Splitting column values iin multiple columns and assigning it to a row

  • December 11, 2013 at 11:31 am

    #1672709

    I hope that you're trying to normalize your data permanently because this will give you headaches.

    To split data you could use the DelimitedSplit8k. Check the article as it's really interesting.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    WITH SampleData([Identity], Name, Col1, Col2, Col3) AS(

    SELECT 1, 'Test1', '1,2,3', '200,300,400', '3,4,6' UNION ALL

    SELECT 2, 'Test2', '3,4,5', '300,455,600', '2,3,8'

    )

    SELECT [Identity],

    Name,

    Split1.Item AS Col1,

    Split2.Item AS Col2,

    Split3.Item AS Col3

    FROM SampleData

    CROSS APPLY dbo.DelimitedSplit8K( Col1, ',') Split1

    CROSS APPLY dbo.DelimitedSplit8K( Col2, ',') Split2

    CROSS APPLY dbo.DelimitedSplit8K( Col3, ',') Split3

    WHERE Split1.ItemNumber = Split2.ItemNumber

    AND Split1.ItemNumber = Split3.ItemNumber

    ORDER BY [Identity], Split1.ItemNumber

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2