none
How to split one column into multiple columns using SELECT STATEMENT

    Question

  • i need to split TEST RESULT into 6 different column using select statement

    plzz help me

    Thanks in Advance

    Tuesday, September 25, 2012 9:22 AM

Answers

  • I embedded full code. Is it not visible to you?

    How about the below:

    Create Table T13(id int identity(1,1),Name varchar(10),Test_result Varchar(1000), Recovery_status varchar(20),[Date] Date) iNSERT INTO T13(Name,Test_result,Recovery_status,[Date]) sELECT 'Srah BIRD','One,two,three,four,five,six','90%',GETDATE() iNSERT INTO T13(Name,Test_result,Recovery_status,[Date]) sELECT 'Richard Ed','one,,three,,five,','99%',GETDATE() ;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 ID,Name, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4, [5] AS Column5,[6] AS Column6 ,Recovery_status,[Date] FROM (SELECT ID,NAME, ROW_NUMBER() OVER (PARTITION BY ID,NAME ORDER BY nums.n) AS PositionInList, LTRIM(RTRIM(SUBSTRING(valueTable.Test_result, nums.n, charindex(N',', valueTable.Test_result + N',', nums.n) - nums.n))) AS [Value] ,Recovery_status,[Date] FROM Numbers AS nums INNER JOIN T13 AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Test_result)) AND SUBSTRING(N',' + valueTable.Test_result, n, 1) = N',') AS SourceTable PIVOT ( MAX([VALUE]) FOR PositionInList IN ([1], [2], [3], [4], [5], [6]) ) AS Table2 Order by ID asc Drop table T13



    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked as answer by Esha2nk Tuesday, September 25, 2012 12:42 PM
    Tuesday, September 25, 2012 11:41 AM

All replies

  • Are you looking for something below:

    Create Table T13(id int identity(1,1),Name varchar(10),Test_result Varchar(1000)) iNSERT INTO T13(Name,Test_result) sELECT 'Srah BIRD','One,two,three,four,five,six' iNSERT INTO T13(Name,Test_result) sELECT 'Richard Ed','one,,three,,five,' ;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 ID,Name, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4, [5] AS Column5,[6] AS Column6 FROM (SELECT ID,NAME, ROW_NUMBER() OVER (PARTITION BY ID,NAME ORDER BY nums.n) AS PositionInList, LTRIM(RTRIM(SUBSTRING(valueTable.Test_result, nums.n, charindex(N',', valueTable.Test_result + N',', nums.n) - nums.n))) AS [Value] FROM Numbers AS nums INNER JOIN T13 AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Test_result)) AND SUBSTRING(N',' + valueTable.Test_result, n, 1) = N',') AS SourceTable PIVOT ( MAX([VALUE]) FOR PositionInList IN ([1], [2], [3], [4], [5], [6]) ) AS Table2 Order by ID asc Drop table T13



    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Proposed as answer by RichardTheKiwi Tuesday, September 25, 2012 9:57 AM
    Tuesday, September 25, 2012 9:38 AM
  • Hi!

    I think you should create standard split function which you could use anywhere you need (not just for this example). Check something like this: http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql

    cheers

    • Proposed as answer by S_Surve Tuesday, September 25, 2012 10:27 AM
    Tuesday, September 25, 2012 9:52 AM
  • another solution:

    declare @T13 Table (id int identity(1,1),Name varchar(10),Test_result Varchar(1000))
    iNSERT INTO @T13(Name,Test_result) sELECT 'Sarah BIRD','One,two,three,four,five,six'
    iNSERT INTO @T13(Name,Test_result) sELECT 'Richard Ed','one,,three,,five,'
    ;with cte as (
    select Name, LEFT(Test_result,CHARINDEX(',',Test_result)-1) as TR1
    ,replace(substring(Test_result,CHARINDEX(',',Test_result)+1,len(Test_result) - CHARINDEX(',',reverse(Test_result))-1 - CHARINDEX(',',Test_result)+1),',','.') as Rest
    ,right(Test_result,CHARINDEX(',',reverse(Test_result))-1) as TR6
    from @T13
    )
    select Name
    ,TR1
    ,parsename(rest,4) as TR2
    ,parsename(rest,3) as TR3
    ,parsename(rest,2) as TR4
    ,parsename(rest,1) as TR5
    ,TR6
     from cte

    Tuesday, September 25, 2012 11:02 AM
  • but how to retive last tow column in same qurey

    (RECOVERY_STATUS and DATE column)

    Tuesday, September 25, 2012 11:25 AM
  • You need to just add it....

    Try the below:

    Create Table T13(id int identity(1,1),Name varchar(10),Test_result Varchar(1000),Recovery_status varchar(20),[Date] Date) iNSERT INTO T13(Name,Test_result,Recovery_status,[Date]) sELECT 'Srah BIRD','One,two,three,four,five,six','90%',GETDATE() iNSERT INTO T13(Name,Test_result,Recovery_status,[Date]) sELECT 'Richard Ed','one,,three,,five,','99%',GETDATE() ;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 ID,Name, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4, [5] AS Column5,[6] AS Column6 ,Recovery_status,[Date] FROM (SELECT ID,NAME, ROW_NUMBER() OVER (PARTITION BY ID,NAME ORDER BY nums.n) AS PositionInList, LTRIM(RTRIM(SUBSTRING(valueTable.Test_result, nums.n, charindex(N',', valueTable.Test_result + N',', nums.n) - nums.n))) AS [Value] ,Recovery_status,[Date] FROM Numbers AS nums INNER JOIN T13 AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Test_result)) AND SUBSTRING(N',' + valueTable.Test_result, n, 1) = N',') AS SourceTable PIVOT ( MAX([VALUE]) FOR PositionInList IN ([1], [2], [3], [4], [5], [6]) ) AS Table2 Order by ID asc Drop table T13



    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Tuesday, September 25, 2012 11:29 AM
  • give me full code please
    Tuesday, September 25, 2012 11:34 AM
  • Declare @temp table (id int, Name varchar(50),Test_Result varchar(100))

    insert into @temp(id,Name,Test_Result)

    select 1,'Srah BIRD','DROPDOWN LIST,SRAH BIRD,FALSE,TRUE,213,YES' UNION ALL

    select 2,'Richander Edward BRAISAIR','CHECK LIST,Richander Edward BRAISAIR,,TRUE,213,YES'

    ;WITH cte

    AS

    (   

    SELECT ID,NAME,

    CAST('<i>' + REPLACE(Test_Result, ',', '</i><i>') + '</i>' AS XML) AS Test_Result  FROM @temp)               

    SELECT     ID,NAME,    x.i.value('.', 'VARCHAR(100)') AS Test_Result

    FROM cte CROSS APPLY Test_Result.nodes('//i') x(i)


    Ramesh Rathod

    Tuesday, September 25, 2012 11:41 AM
  • I embedded full code. Is it not visible to you?

    How about the below:

    Create Table T13(id int identity(1,1),Name varchar(10),Test_result Varchar(1000), Recovery_status varchar(20),[Date] Date) iNSERT INTO T13(Name,Test_result,Recovery_status,[Date]) sELECT 'Srah BIRD','One,two,three,four,five,six','90%',GETDATE() iNSERT INTO T13(Name,Test_result,Recovery_status,[Date]) sELECT 'Richard Ed','one,,three,,five,','99%',GETDATE() ;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 ID,Name, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4, [5] AS Column5,[6] AS Column6 ,Recovery_status,[Date] FROM (SELECT ID,NAME, ROW_NUMBER() OVER (PARTITION BY ID,NAME ORDER BY nums.n) AS PositionInList, LTRIM(RTRIM(SUBSTRING(valueTable.Test_result, nums.n, charindex(N',', valueTable.Test_result + N',', nums.n) - nums.n))) AS [Value] ,Recovery_status,[Date] FROM Numbers AS nums INNER JOIN T13 AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Test_result)) AND SUBSTRING(N',' + valueTable.Test_result, n, 1) = N',') AS SourceTable PIVOT ( MAX([VALUE]) FOR PositionInList IN ([1], [2], [3], [4], [5], [6]) ) AS Table2 Order by ID asc Drop table T13



    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked as answer by Esha2nk Tuesday, September 25, 2012 12:42 PM
    Tuesday, September 25, 2012 11:41 AM