locked
how to Split comma separated values in consequtive Column RRS feed

  • Question

  • Hi friend,

    I am facing a problem with comma separated value. I have a long string and I need to create a view where that text separated by comma(,) with individual row. So far I have found is

    SELECT     Address, SUBSTRING(Address, 1, CHARINDEX(',', Address) - 1) AS loc1, SUBSTRING(Address, CHARINDEX(',', Address) + 1, LEN(Address)) AS lco2
    FROM         TrackInfo

    Lets assume my Address is "Loc1, Loc2, Loc3, Loc4"
    After execute the output becomes Loc1 | Loc2, Loc3, Loc4.

    But What I need is

    Loc1 | Loc2 | Loc3 | Loc4

    I dont know where I am missing. I do not need to create extra table. I need this in a view.

     Please correct it for me.

    Thanks & Regards


    Monday, October 28, 2013 11:38 AM

Answers

  • Hi,

    Try this link .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, October 28, 2013 4:09 PM

All replies

  • Try the below:

    Create Table Product(Col1 varchar(10),Col2 Varchar(1000))
    
    iNSERT INTO pRODUCT(Col1,Col2) sELECT 1,'Loc1, Loc2, Loc3, Loc4'
    
     
        
    ;WITH
    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
    L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
    L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
    Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
    SELECT Col1, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4
    FROM
    (SELECT Col1,
            ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY nums.n) AS PositionInList,
            LTRIM(RTRIM(SUBSTRING(valueTable.Col2, nums.n, charindex(N', ', valueTable.Col2 + N', ', nums.n) - nums.n))) AS [Value]
     FROM   Numbers AS nums INNER JOIN Product AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Col2)) AND SUBSTRING(N', ' + valueTable.Col2, n, 1) = N', ') AS SourceTable
    PIVOT
    (
    MAX([VALUE]) FOR PositionInList IN ([1], [2], [3], [4])
    ) AS Table2
    
    Drop table Product
    

    Ref: http://beyondrelational.com/modules/2/blogs/557/posts/19519/t-sql-script-splitting-a-delimited-column-value-to-multiple-columns.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 28, 2013 11:46 AM
    Answerer
  • Ya I have run, Its working But How can I convert it into view in the address column?

    I have a column named address. I need to use that code in that column address.

    Thanks

    Monday, October 28, 2013 11:58 AM
  • Are you looking for the below:

    Create Table Product(Col1 varchar(10),Col2 Varchar(1000))
    
    iNSERT INTO pRODUCT(Col1,Col2) sELECT 1,'Loc1, Loc2, Loc3, Loc4'
    
     
    create view vw_test1
    as
    WITH
    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
    L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
    L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
    Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
    SELECT Col1, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4
    FROM
    (SELECT Col1,
            ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY nums.n) AS PositionInList,
            LTRIM(RTRIM(SUBSTRING(valueTable.Col2, nums.n, charindex(N', ', valueTable.Col2 + N', ', nums.n) - nums.n))) AS [Value]
     FROM   Numbers AS nums INNER JOIN Product AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Col2)) AND SUBSTRING(N', ' + valueTable.Col2, n, 1) = N', ') AS SourceTable
    PIVOT
    (
    MAX([VALUE]) FOR PositionInList IN ([1], [2], [3], [4])
    ) AS Table2
    
    Select * From vw_test1
    
    Drop table Product


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 28, 2013 1:24 PM
    Answerer
  • Actually I will apply this in a column of data. May be there will be thousand of address and I need to separate all the  addresses in consecutive columns. I do not know how to use this in my column.
    Monday, October 28, 2013 3:47 PM
  • Hi,

    Try this link .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, October 28, 2013 4:09 PM
  • Sathya,

    Please check out this thread: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/64ad4f52-2fd8-4266-b4a4-5657c8870246/needed-more-answerers?forum=sqlgetstarted

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    • Proposed as answer by Heidi-Duan Monday, November 4, 2013 1:02 PM
    • Unproposed as answer by SathyanarrayananS Monday, November 4, 2013 3:18 PM
    Monday, November 4, 2013 7:16 AM