locked
Put one column data into many columns RRS feed

  • Question

  • Hello all,

    how to split a single column into multiple columns with | delimited. For example I have column old_values in this I have a data with | delimited and I need to split this data into multiple columns.

    Old_values

    xxx|yyy|zzz|aaa|bbb|ccc|ddd

    into

    A     B       C        D       E        F       G

    xxx  yyy    zzz     aaa    bbb     ccc    ddd

    can any one help me regarding this. Ask me for any questions.

    Thanks,

    Sidhu

    Thursday, October 15, 2015 2:04 PM

Answers

  • create table test(col1 varchar(10), col3 varchar(100))
    insert into test values('a', 'xxx|yyy|zzz|aaa|bbb|ccc|ddd')
     
    
    
     --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
      
     select col1, IDENTITY(int, 1,1) id
     , substring( col3 , n, charindex('|', col3 + '|', n) - n)  cols
    into mytemp
    from test
    cross apply (select n from Nums) d 
    Where n <= len( col3 ) AND substring(',' + col3 , n, 1) = '|'
      
     
     
     ;with mycte as (Select * , row_number() Over(Partition by col1 Order by id ) rn 
     from mytemp
     )
      
      
     Select  Col1  
     , Max(Case when rn=1 then cols End) r1 
     , Max(Case when rn=2 then cols End) r2 
     , Max(Case when rn=3 then cols End) r3 
     , Max(Case when rn=4 then cols End) r4 
     , Max(Case when rn=5 then cols End) r5
      , Max(Case when rn=6 then cols End) r6 
       , Max(Case when rn=7 then cols End) r7 
        , Max(Case when rn=8 then cols End) r8
      
     from mycte
     Group by  Col1 
      
     drop table mytemp
     
     
    drop table  test

    • Marked as answer by siddu_123 Thursday, October 15, 2015 2:34 PM
    Thursday, October 15, 2015 2:18 PM
  • You can use any splitter function combined with a pivot to get the job done:

    SELECT *
      FROM (
      SELECT 'xxx|yyy|zzz|aaa|bbb|ccc|ddd' AS oldValue, CHAR((id+64)) AS col, value
        FROM dbo.splitter('xxx|yyy|zzz|aaa|bbb|ccc|ddd','|')
    	   ) s
    	  PIVOT (
    	         MAX(value) FOR col IN (A,B,C,D,E,F,G)
    			) p


    Here we're grabbing the data, converting the numeric ID column thats returned from the splitter function to a letter (as you wanted letter column names) and putting the result into a pivot on those letter values.

    Presto, chango:

    oldValue					A	B	C	D	E	F	G
    xxx|yyy|zzz|aaa|bbb|ccc|ddd	xxx	yyy	zzz	aaa	bbb	ccc	ddd

    If the values you want to split are in a table, it'll look something like this:

    DECLARE @myTable TABLE (oldValue NVARCHAR(50))
    INSERT INTO @myTable (oldValue) VALUES
    ('xxx|yyy|zzz|aaa|bbb|ccc|ddd')
    
    SELECT *
      FROM (
      SELECT oldValue, CHAR((id+64)) AS col, value
        FROM @myTable 
    	  CROSS APPLY dbo.splitter(oldValue,'|')
    	   ) s
    	  PIVOT (
    	         MAX(value) FOR col IN (A,B,C,D,E,F,G)
    			) p


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    • Edited by Patrick Hurst Thursday, October 15, 2015 2:48 PM
    • Marked as answer by siddu_123 Thursday, October 15, 2015 7:15 PM
    Thursday, October 15, 2015 2:46 PM
  • You can use a SPLIT function. Here is the sample code:


     --Check UDF  [dbo].[split]    drop and create
    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[split]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ) ) 
    Drop Function  [dbo].[split]
    Go


    CREATE FUNCTION [dbo].[split]
    ( @String NVARCHAR(4000), @Delimiter NCHAR(1) )
                 RETURNS TABLE
                 AS
                 RETURN
                 (
                    With Split(stpos, endpos)
                    AS (
                        SELECT 0 AS stpos, CHARINDEX(@Delimiter, @String) AS endpos
                        UNION ALL
                        SELECT endpos + 1, CHARINDEX(@Delimiter, @String, endpos + 1)
                            FROM Split
                            WHERE endpos > 0
                 )
                 SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT 1))  as id,
                    SUBSTRING(@String, stpos, COALESCE(NULLIF(endpos,0), LEN(@String)+1)-stpos) as Data
                 FROM Split
                  )



    Go


    create table test(col1 varchar(10), col3 varchar(100))
    insert into test values('a', 'xxx|yyy|zzz|aaa|bbb|ccc|ddd')
     
     ;with mycte as (select * from test t

    CROSS APPLY dbo.Split(  t.col3,'|'))

    SELECT  col1
    , [1] as A1,[2] as A2,[3] as A3,[4] as A4, [5] as A5,[6] as A6,[7] as A7, [8] as A8 
    FROM 
    ( Select * FROM mycte ) src

    PIVOT( MAX(data) FOR id in ([1],[2],[3],[4],[5],[6],[7],[8])) pvt

     
     
     
    drop table  test


    • Proposed as answer by Naomi N Thursday, October 15, 2015 3:29 PM
    • Marked as answer by siddu_123 Thursday, October 15, 2015 7:15 PM
    Thursday, October 15, 2015 2:49 PM
  • You'll have to create the function I linked to in the original post.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Thursday, October 15, 2015 3:29 PM
    Thursday, October 15, 2015 3:14 PM
  • It can also be achieved using XML.

    The following code illustrates it.

    --
    
    DECLARE @S varchar(max),@Split char(1),@X xml
    
    SELECT @S = 'aaa|bbb|ccc|ddd|eee|ff',@Split = '|'
     
    SELECT @X = CONVERT(xml,' <root> <s>' + REPLACE(@S,@Split,'</s> <s>') + '</s>   </root> ') 
    
    SELECT T.c.value('.','varchar(20)') as Value INTO #temps
    FROM @X.nodes('/root/s') T(c) 
    
    
    
    
    
    
    DECLARE
        @SQL varchar(MAX),
        @ColumnList varchar(MAX)
    
    SELECT @ColumnList=
           COALESCE(@ColumnList + ',','') + QUOTENAME(Value)
    FROM
    (
           SELECT DISTINCT Value
           FROM #temps 
    ) T
    
    
    SET @SQL = '
    WITH PivotData AS
    (
           SELECT Value
           FROM #temps
    )
    SELECT
        ' + @ColumnList + '
    FROM
        PivotData
    PIVOT
    (
        MAX(Value)
        FOR Value
        IN (' + @ColumnList + ')
    ) AS PivotResult'
    
    EXEC (@SQL)
    
    DROP TABLE  #temps


    Aparna

    • Proposed as answer by appsqldev Thursday, October 15, 2015 9:11 PM
    • Marked as answer by Eric__Zhang Friday, October 16, 2015 1:52 AM
    Thursday, October 15, 2015 9:10 PM
  • xml is tantamount to witchcraft.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:09 PM
    Friday, October 16, 2015 2:21 PM
  • Hello all,

    From the above code(s) we are hard coding by giving the sample data
    SELECT @S = 'aaa|bbb|ccc|ddd|eee|ff',@Split = '|'.I want to split the data from a column called "old_values" so ones the function is called it should split the column data split by pipe(|). No hard coding. Could any one help me on this.


    Thanks,
    Sidhu
    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:09 PM
    Monday, October 19, 2015 3:33 PM
  • Siddu, my example uses a table variable.. just substitute your table in its place, like this:

    SELECT *
      FROM (
      SELECT oldValue, CHAR((id+64)) AS col, value
        FROM yourTableNameGoesHere 
    	  CROSS APPLY dbo.splitter(oldValue,'|')
    	   ) s
    	  PIVOT (
    	         MAX(value) FOR col IN (A,B,C,D,E,F,G)
    			) p


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    • Edited by Patrick Hurst Monday, October 19, 2015 4:25 PM
    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:09 PM
    Monday, October 19, 2015 4:24 PM
  • Try this:

    SELECT *
      FROM (
      SELECT Old_Values, CHAR((id+64)) AS col, value
        FROM Audit_table
     CROSS APPLY dbo.fn_Split(Old_Values,'|')
      ) s
     PIVOT (
            MAX(value) FOR col IN (A,B,C,D,E,F,G)
    ) p

    You had changed col to old_values in your code. It was looking for the values A,B,C etc in that column.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:09 PM
    Monday, October 19, 2015 5:42 PM
  • I wonder if your splitter function works differently to mine, because this seems to work the way you want:

    DECLARE @Audit_table TABLE (old_values NVARCHAR(MAX))
    INSERT INTO @Audit_table (old_values) VALUES
    ('ACR|"The ACR criteria are one way of determining efficacy of drugs at treating rheumatoid arthritis. Devised by the American College of Rheumatology, an ACR 20 response requires a patient to have a 20% reduction in the number of swollen and tender joints, together with a 20% improvement in three of five of the following criteria:  • Blood-borne inflammatory markers (CRP or ESR)  • Physician assessment  • Patient assessment  • Pain scale  • Disability/functional questionnaire"|"ACR criteria is indicated as ACR 20, ACR 50, ACR 70, and ACR 90."|http://www.hopkins-arthritis.org/physician-corner/education/acr/acr.html|Blank Created_By|Blank Updated_By.')
    
    SELECT *
      FROM (
      SELECT Old_Values, CHAR((id+64)) AS col, value
        FROM @Audit_table
     CROSS APPLY dbo.splitter(Old_Values,'|')
      ) s
     PIVOT (
            MAX(value) FOR col IN (A,B,C,D,E,F,G)
    ) p

    Old_Values	A	B	C	D	E	F	G
    ACR|"The ACR criteria are one way of determining efficacy of drugs at treating rheumatoid arthritis. Devised by the American College of Rheumatology, an ACR 20 response requires a patient to have a 20% reduction in the number of swollen and tender joints, together with a 20% improvement in three of five of the following criteria:  • Blood-borne inflammatory markers (CRP or ESR)  • Physician assessment  • Patient assessment  • Pain scale  • Disability/functional questionnaire"|"ACR criteria is indicated as ACR 20, ACR 50, ACR 70, and ACR 90."|http://www.hopkins-arthritis.org/physician-corner/education/acr/acr.html|Blank Created_By|Blank Updated_By.	ACR	"The ACR criteria are one way of determining efficacy of drugs at treating rheumatoid arthritis. Devised by the American College of Rheumatology, an ACR 20 response requires a patient to have a 20% reduction in the number of swollen and tender joints, together with a 20% improvement in three of five of the following criteria:  • Blood-borne inflammatory markers (CRP or ESR)  • Physician assessment  • Patient assessment  • Pain scale  • Disability/functional questionnaire"	"ACR criteria is indicated as ACR 20, ACR 50, ACR 70, and ACR 90."	http://www.hopkins-arthritis.org/physician-corner/education/acr/acr.html	Blank Created_By	Blank Updated_By.	NULL


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:09 PM
    Monday, October 19, 2015 6:59 PM
  • If your inserting into a table, simply follow standard INSERT syntax:

    INSERT INTO myDestinationTable (name, deffinition, ...)
    SELECT *
      FROM (
      SELECT Old_Values, CHAR((id+64)) AS col, value
        FROM Audit_table
     CROSS APPLY dbo.splitter(Old_Values,'|')
      ) s
     PIVOT (
            MAX(value) FOR col IN (A,B,C,D,E,F,G)
    ) p


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:08 PM
    Monday, October 19, 2015 8:16 PM
  • If you simply want different alias' then define them:

    DECLARE @Audit_table TABLE (old_values NVARCHAR(MAX))
    INSERT INTO @Audit_table (old_values) VALUES
    ('ACR|"The ACR criteria are one way of determining efficacy of drugs at treating rheumatoid arthritis. Devised by the American College of Rheumatology, an ACR 20 response requires a patient to have a 20% reduction in the number of swollen and tender joints, together with a 20% improvement in three of five of the following criteria:  • Blood-borne inflammatory markers (CRP or ESR)  • Physician assessment  • Patient assessment  • Pain scale  • Disability/functional questionnaire"|"ACR criteria is indicated as ACR 20, ACR 50, ACR 70, and ACR 90."|http://www.hopkins-arthritis.org/physician-corner/education/acr/acr.html|Blank Created_By|Blank Updated_By.')
    
    
    
    SELECT old_values, a AS name, b AS deffinition, c, d, e, f, g
      FROM (
      SELECT Old_Values, CHAR((id+64)) AS col, value
        FROM @Audit_table
     CROSS APPLY dbo.splitter(Old_Values,'|')
      ) s
     PIVOT (
            MAX(value) FOR col IN (A,B,C,D,E,F,G)
    ) p


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:08 PM
    Monday, October 19, 2015 8:17 PM
  • Ug. How many times do I have to explain that the table variables are just for demonstration... simply replace it with your table name...

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:08 PM
    Monday, October 19, 2015 9:58 PM
  • I've given you the answer... just use an ALIAS... 

    SELECT A AS definition, B AS Notes

    and so on.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:08 PM
    Tuesday, October 20, 2015 2:35 PM
  • If you run this:

    DECLARE @Audit_table TABLE (old_values NVARCHAR(MAX))
    INSERT INTO @Audit_table (old_values) VALUES
    ('ACR|"The ACR criteria are one way of determining efficacy of drugs at treating rheumatoid arthritis. Devised by the American College of Rheumatology, an ACR 20 response requires a patient to have a 20% reduction in the number of swollen and tender joints, together with a 20% improvement in three of five of the following criteria:  • Blood-borne inflammatory markers (CRP or ESR)  • Physician assessment  • Patient assessment  • Pain scale  • Disability/functional questionnaire"|"ACR criteria is indicated as ACR 20, ACR 50, ACR 70, and ACR 90."|http://www.hopkins-arthritis.org/physician-corner/education/acr/acr.html|Blank Created_By|Blank Updated_By.')
    
    
    
    SELECT old_values, a AS name, b AS deffinition, c, d, e, f, g
      FROM (
      SELECT Old_Values, CHAR((id+64)) AS col, value
        FROM @Audit_table
     CROSS APPLY dbo.splitter(Old_Values,'|')
      ) s
     PIVOT (
            MAX(value) FOR col IN (A,B,C,D,E,F,G)
    ) p

    You get columns, old_value, name, deffinition, c, d, e, f, g..

    You just need to map the other columns like this...


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:08 PM
    Tuesday, October 20, 2015 4:19 PM

All replies

  • create table test(col1 varchar(10), col3 varchar(100))
    insert into test values('a', 'xxx|yyy|zzz|aaa|bbb|ccc|ddd')
     
    
    
     --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
      
     select col1, IDENTITY(int, 1,1) id
     , substring( col3 , n, charindex('|', col3 + '|', n) - n)  cols
    into mytemp
    from test
    cross apply (select n from Nums) d 
    Where n <= len( col3 ) AND substring(',' + col3 , n, 1) = '|'
      
     
     
     ;with mycte as (Select * , row_number() Over(Partition by col1 Order by id ) rn 
     from mytemp
     )
      
      
     Select  Col1  
     , Max(Case when rn=1 then cols End) r1 
     , Max(Case when rn=2 then cols End) r2 
     , Max(Case when rn=3 then cols End) r3 
     , Max(Case when rn=4 then cols End) r4 
     , Max(Case when rn=5 then cols End) r5
      , Max(Case when rn=6 then cols End) r6 
       , Max(Case when rn=7 then cols End) r7 
        , Max(Case when rn=8 then cols End) r8
      
     from mycte
     Group by  Col1 
      
     drop table mytemp
     
     
    drop table  test

    • Marked as answer by siddu_123 Thursday, October 15, 2015 2:34 PM
    Thursday, October 15, 2015 2:18 PM
  • Hi Jingyang Li,

    Thanks for the reply and the answer. Is there any other way we can do it, I am not able to understand the code sorry about it

    Thursday, October 15, 2015 2:34 PM
  • You can use any splitter function combined with a pivot to get the job done:

    SELECT *
      FROM (
      SELECT 'xxx|yyy|zzz|aaa|bbb|ccc|ddd' AS oldValue, CHAR((id+64)) AS col, value
        FROM dbo.splitter('xxx|yyy|zzz|aaa|bbb|ccc|ddd','|')
    	   ) s
    	  PIVOT (
    	         MAX(value) FOR col IN (A,B,C,D,E,F,G)
    			) p


    Here we're grabbing the data, converting the numeric ID column thats returned from the splitter function to a letter (as you wanted letter column names) and putting the result into a pivot on those letter values.

    Presto, chango:

    oldValue					A	B	C	D	E	F	G
    xxx|yyy|zzz|aaa|bbb|ccc|ddd	xxx	yyy	zzz	aaa	bbb	ccc	ddd

    If the values you want to split are in a table, it'll look something like this:

    DECLARE @myTable TABLE (oldValue NVARCHAR(50))
    INSERT INTO @myTable (oldValue) VALUES
    ('xxx|yyy|zzz|aaa|bbb|ccc|ddd')
    
    SELECT *
      FROM (
      SELECT oldValue, CHAR((id+64)) AS col, value
        FROM @myTable 
    	  CROSS APPLY dbo.splitter(oldValue,'|')
    	   ) s
    	  PIVOT (
    	         MAX(value) FOR col IN (A,B,C,D,E,F,G)
    			) p


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    • Edited by Patrick Hurst Thursday, October 15, 2015 2:48 PM
    • Marked as answer by siddu_123 Thursday, October 15, 2015 7:15 PM
    Thursday, October 15, 2015 2:46 PM
  • You can use a SPLIT function. Here is the sample code:


     --Check UDF  [dbo].[split]    drop and create
    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[split]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ) ) 
    Drop Function  [dbo].[split]
    Go


    CREATE FUNCTION [dbo].[split]
    ( @String NVARCHAR(4000), @Delimiter NCHAR(1) )
                 RETURNS TABLE
                 AS
                 RETURN
                 (
                    With Split(stpos, endpos)
                    AS (
                        SELECT 0 AS stpos, CHARINDEX(@Delimiter, @String) AS endpos
                        UNION ALL
                        SELECT endpos + 1, CHARINDEX(@Delimiter, @String, endpos + 1)
                            FROM Split
                            WHERE endpos > 0
                 )
                 SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT 1))  as id,
                    SUBSTRING(@String, stpos, COALESCE(NULLIF(endpos,0), LEN(@String)+1)-stpos) as Data
                 FROM Split
                  )



    Go


    create table test(col1 varchar(10), col3 varchar(100))
    insert into test values('a', 'xxx|yyy|zzz|aaa|bbb|ccc|ddd')
     
     ;with mycte as (select * from test t

    CROSS APPLY dbo.Split(  t.col3,'|'))

    SELECT  col1
    , [1] as A1,[2] as A2,[3] as A3,[4] as A4, [5] as A5,[6] as A6,[7] as A7, [8] as A8 
    FROM 
    ( Select * FROM mycte ) src

    PIVOT( MAX(data) FOR id in ([1],[2],[3],[4],[5],[6],[7],[8])) pvt

     
     
     
    drop table  test


    • Proposed as answer by Naomi N Thursday, October 15, 2015 3:29 PM
    • Marked as answer by siddu_123 Thursday, October 15, 2015 7:15 PM
    Thursday, October 15, 2015 2:49 PM
  • I just used this recently... 

    After using bulk insert to import a file into a 1 column table, I was then able to choose specific  rows to import into the multi-column table.

    CREATE FUNCTION [dbo].[fn_GetColumn](@source VARCHAR(5000), @column INT, @delimiter VARCHAR(10)=',') RETURNS VARCHAR(5000) as 
    begin
    /* within a given delimited string, this function returns the requested column. column 1 starts a 0 */
    DECLARE @returnValue VARCHAR(5000)
    DECLARE @workingOn INT
    DECLARE @length INT

    SET @workingOn=0

    WHILE @workingOn<@column 
    BEGIN
    SET @source= SUBSTRING(@source,CHARINDEX(@delimiter,@source)+1,5000)
    SET @workingOn+=1
    END

    SET @length= CHARINDEX(@delimiter,@source)
    SET @length= CASE WHEN @length=0 THEN 5000 ELSE @length-1 END
    SET @returnValue=SUBSTRING(@source,1,@length)

    RETURN @returnValue
    end;

    SELECT dbo.getcolumn(line,0,'|'), dbo.getcolumn(line,1,'|'), dbo.getcolumn(line,2,'|'),dbo.getcolumn(line,3,'|'), dbo.getcolumn(line,4,'|'), dbo.getcolumn(line,5,'|'),dbo.getcolumn(line,6,'|'),dbo.getcolumn(line,7,'|'),
    dbo.getcolumn(line,8,'|'),dbo.getcolumn(line,9,'|'),dbo.getcolumn(line,10,'|'),dbo.getcolumn(line,11,'|'),dbo.getcolumn(line,12,'|'),dbo.getcolumn(line,13,'|'),dbo.getcolumn(line,14,'|'),dbo.getcolumn(line,15,'|'),
    dbo.getcolumn(line,16,'|'),dbo.getcolumn(line,17,'|'),dbo.getcolumn(line,18,'|'),dbo.getcolumn(line,19,'|'),dbo.getcolumn(line,20,'|'),dbo.getcolumn(line,21,'|'),dbo.getcolumn(line,22,'|'),
    dbo.getcolumn(line,23,'|'),dbo.getcolumn(line,24,'|'),dbo.getcolumn(line,25,'|'),dbo.getcolumn(line,26,'|'),dbo.getcolumn(line,27,'|')
     from MyTMPTable

    Where len(line)-len(replace(line,'|','')) = 28;

    • Edited by JohnGman Thursday, October 15, 2015 3:01 PM
    Thursday, October 15, 2015 2:59 PM
  • Hello Patrick,

    Thanks for the reply when I tried to execute this, I am getting invalid object name dbo.splitter.

    • Marked as answer by siddu_123 Thursday, October 15, 2015 7:15 PM
    • Unmarked as answer by Naomi N Thursday, October 15, 2015 9:13 PM
    Thursday, October 15, 2015 3:07 PM
  • You'll have to create the function I linked to in the original post.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Thursday, October 15, 2015 3:29 PM
    Thursday, October 15, 2015 3:14 PM
  • Hello Patrick,

    I have a question what is the ID column and where its is coming from.

    Thursday, October 15, 2015 3:30 PM
  • the ID column is returned from the splitter function. It's basically the split number, id 1 is the first item, 2 is the second, and so on. It allows you to filter the result if desired.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Thursday, October 15, 2015 6:53 PM
  • It can also be achieved using XML.

    The following code illustrates it.

    --
    
    DECLARE @S varchar(max),@Split char(1),@X xml
    
    SELECT @S = 'aaa|bbb|ccc|ddd|eee|ff',@Split = '|'
     
    SELECT @X = CONVERT(xml,' <root> <s>' + REPLACE(@S,@Split,'</s> <s>') + '</s>   </root> ') 
    
    SELECT T.c.value('.','varchar(20)') as Value INTO #temps
    FROM @X.nodes('/root/s') T(c) 
    
    
    
    
    
    
    DECLARE
        @SQL varchar(MAX),
        @ColumnList varchar(MAX)
    
    SELECT @ColumnList=
           COALESCE(@ColumnList + ',','') + QUOTENAME(Value)
    FROM
    (
           SELECT DISTINCT Value
           FROM #temps 
    ) T
    
    
    SET @SQL = '
    WITH PivotData AS
    (
           SELECT Value
           FROM #temps
    )
    SELECT
        ' + @ColumnList + '
    FROM
        PivotData
    PIVOT
    (
        MAX(Value)
        FOR Value
        IN (' + @ColumnList + ')
    ) AS PivotResult'
    
    EXEC (@SQL)
    
    DROP TABLE  #temps


    Aparna

    • Proposed as answer by appsqldev Thursday, October 15, 2015 9:11 PM
    • Marked as answer by Eric__Zhang Friday, October 16, 2015 1:52 AM
    Thursday, October 15, 2015 9:10 PM
  • xml is tantamount to witchcraft.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:09 PM
    Friday, October 16, 2015 2:21 PM
  • Hello all,

    From the above code(s) we are hard coding by giving the sample data
    SELECT @S = 'aaa|bbb|ccc|ddd|eee|ff',@Split = '|'.I want to split the data from a column called "old_values" so ones the function is called it should split the column data split by pipe(|). No hard coding. Could any one help me on this.


    Thanks,
    Sidhu
    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:09 PM
    Monday, October 19, 2015 3:33 PM
  • Siddu, my example uses a table variable.. just substitute your table in its place, like this:

    SELECT *
      FROM (
      SELECT oldValue, CHAR((id+64)) AS col, value
        FROM yourTableNameGoesHere 
    	  CROSS APPLY dbo.splitter(oldValue,'|')
    	   ) s
    	  PIVOT (
    	         MAX(value) FOR col IN (A,B,C,D,E,F,G)
    			) p


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    • Edited by Patrick Hurst Monday, October 19, 2015 4:25 PM
    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:09 PM
    Monday, October 19, 2015 4:24 PM
  • Try this:

    SELECT *
      FROM (
      SELECT Old_Values, CHAR((id+64)) AS col, value
        FROM Audit_table
     CROSS APPLY dbo.fn_Split(Old_Values,'|')
      ) s
     PIVOT (
            MAX(value) FOR col IN (A,B,C,D,E,F,G)
    ) p

    You had changed col to old_values in your code. It was looking for the values A,B,C etc in that column.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:09 PM
    Monday, October 19, 2015 5:42 PM
  • hi Patrick,

    when I executed I got the following output:

    Old_values                                                                                            a      b            c                                           1|Some kind of invalid character in the definition for                              NULL     NULL     NULL ........

    COSMIC.|0|May 20 2015  3:45PM|Blank Updated_On         

    Could you please help me with this         


    Monday, October 19, 2015 5:48 PM
  • I wonder if your splitter function works differently to mine, because this seems to work the way you want:

    DECLARE @Audit_table TABLE (old_values NVARCHAR(MAX))
    INSERT INTO @Audit_table (old_values) VALUES
    ('ACR|"The ACR criteria are one way of determining efficacy of drugs at treating rheumatoid arthritis. Devised by the American College of Rheumatology, an ACR 20 response requires a patient to have a 20% reduction in the number of swollen and tender joints, together with a 20% improvement in three of five of the following criteria:  • Blood-borne inflammatory markers (CRP or ESR)  • Physician assessment  • Patient assessment  • Pain scale  • Disability/functional questionnaire"|"ACR criteria is indicated as ACR 20, ACR 50, ACR 70, and ACR 90."|http://www.hopkins-arthritis.org/physician-corner/education/acr/acr.html|Blank Created_By|Blank Updated_By.')
    
    SELECT *
      FROM (
      SELECT Old_Values, CHAR((id+64)) AS col, value
        FROM @Audit_table
     CROSS APPLY dbo.splitter(Old_Values,'|')
      ) s
     PIVOT (
            MAX(value) FOR col IN (A,B,C,D,E,F,G)
    ) p

    Old_Values	A	B	C	D	E	F	G
    ACR|"The ACR criteria are one way of determining efficacy of drugs at treating rheumatoid arthritis. Devised by the American College of Rheumatology, an ACR 20 response requires a patient to have a 20% reduction in the number of swollen and tender joints, together with a 20% improvement in three of five of the following criteria:  • Blood-borne inflammatory markers (CRP or ESR)  • Physician assessment  • Patient assessment  • Pain scale  • Disability/functional questionnaire"|"ACR criteria is indicated as ACR 20, ACR 50, ACR 70, and ACR 90."|http://www.hopkins-arthritis.org/physician-corner/education/acr/acr.html|Blank Created_By|Blank Updated_By.	ACR	"The ACR criteria are one way of determining efficacy of drugs at treating rheumatoid arthritis. Devised by the American College of Rheumatology, an ACR 20 response requires a patient to have a 20% reduction in the number of swollen and tender joints, together with a 20% improvement in three of five of the following criteria:  • Blood-borne inflammatory markers (CRP or ESR)  • Physician assessment  • Patient assessment  • Pain scale  • Disability/functional questionnaire"	"ACR criteria is indicated as ACR 20, ACR 50, ACR 70, and ACR 90."	http://www.hopkins-arthritis.org/physician-corner/education/acr/acr.html	Blank Created_By	Blank Updated_By.	NULL


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:09 PM
    Monday, October 19, 2015 6:59 PM
  • Hi patrick,

    Thanks a lot for the replies but I don't want the out put as such.I want like in the Old_values column we have a data with PIPES(|) separated .Now let say first is ACR when I execute my stored proc. The stored proc should call the function so that the first word of every old_values column data should be in name column and now the word (s) between   second pipe to third pipe should be in definition column as so on. Could you pleas help me with this. Ask me for any questions.

    Thanks,

    Sidhu

    Monday, October 19, 2015 7:11 PM
  • If your inserting into a table, simply follow standard INSERT syntax:

    INSERT INTO myDestinationTable (name, deffinition, ...)
    SELECT *
      FROM (
      SELECT Old_Values, CHAR((id+64)) AS col, value
        FROM Audit_table
     CROSS APPLY dbo.splitter(Old_Values,'|')
      ) s
     PIVOT (
            MAX(value) FOR col IN (A,B,C,D,E,F,G)
    ) p


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:08 PM
    Monday, October 19, 2015 8:16 PM
  • If you simply want different alias' then define them:

    DECLARE @Audit_table TABLE (old_values NVARCHAR(MAX))
    INSERT INTO @Audit_table (old_values) VALUES
    ('ACR|"The ACR criteria are one way of determining efficacy of drugs at treating rheumatoid arthritis. Devised by the American College of Rheumatology, an ACR 20 response requires a patient to have a 20% reduction in the number of swollen and tender joints, together with a 20% improvement in three of five of the following criteria:  • Blood-borne inflammatory markers (CRP or ESR)  • Physician assessment  • Patient assessment  • Pain scale  • Disability/functional questionnaire"|"ACR criteria is indicated as ACR 20, ACR 50, ACR 70, and ACR 90."|http://www.hopkins-arthritis.org/physician-corner/education/acr/acr.html|Blank Created_By|Blank Updated_By.')
    
    
    
    SELECT old_values, a AS name, b AS deffinition, c, d, e, f, g
      FROM (
      SELECT Old_Values, CHAR((id+64)) AS col, value
        FROM @Audit_table
     CROSS APPLY dbo.splitter(Old_Values,'|')
      ) s
     PIVOT (
            MAX(value) FOR col IN (A,B,C,D,E,F,G)
    ) p


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:08 PM
    Monday, October 19, 2015 8:17 PM
  • hey Patrick I don't want to hard code it. I just want to give the column name so it should split it up.

    INSERT INTO @Audit_table (old_values) VALUES
    ('ACR|"The ACR criteria are one way of determining efficacy of drugs at treating rheumatoid arthritis. Devised by the American College of Rheumatology, an ACR 20 response requires a patient to have a 20% reduction in the number of swollen and tender joints, together with a 20% improvement in three of five of the following criteria:  • Blood-borne inflammatory markers (CRP or ESR)  • Physician assessment  • Patient assessment  • Pain scale  • Disability/functional questionnaire"|"ACR criteria is indicated as ACR 20, ACR 50, ACR 70, and ACR 90."|http://www.hopkins-arthritis.org/physician-corner/education/acr/acr.html|Blank Created_By|Blank Updated_By.')
    

    I don't to hard code it. And also the out put is same I don't want that output it should get with 6 columnsseperate

    Monday, October 19, 2015 8:37 PM
  • Ug. How many times do I have to explain that the table variables are just for demonstration... simply replace it with your table name...

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:08 PM
    Monday, October 19, 2015 9:58 PM
  • Hello Patrick,

    I have replaced with my table but still getting the same out put. I have to parse the old_values column to  5 columns like Name, Definition, Notes, Link, Context, Created_By, Updated_By.And here is the sample data of old_column:

    ADM|Außendienstmitarbeiter|(German)|Blank Link|Blank Created_By|Sam.

    Name      definition                        Notes            Link             Context    Created_By       Updated_By

    ADM        Außendienstmitarbeiter    (German)      Blank Link                     Blank Created_By         Sam

    Please it's very urgent need to do this in one hour. Can any one help me.

    Thanks,

    Sidhu


    • Edited by siddu_123 Tuesday, October 20, 2015 2:17 PM
    Tuesday, October 20, 2015 2:16 PM
  • I've given you the answer... just use an ALIAS... 

    SELECT A AS definition, B AS Notes

    and so on.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:08 PM
    Tuesday, October 20, 2015 2:35 PM
  • Hello Patrick,

    I am still getting the same old output.. could you please check with the pivot...

    and if you have any other method rather than this it could be very helpfull

    Thanks,

    Sidhu


    • Edited by siddu_123 Tuesday, October 20, 2015 4:04 PM
    Tuesday, October 20, 2015 4:03 PM
  • If you run this:

    DECLARE @Audit_table TABLE (old_values NVARCHAR(MAX))
    INSERT INTO @Audit_table (old_values) VALUES
    ('ACR|"The ACR criteria are one way of determining efficacy of drugs at treating rheumatoid arthritis. Devised by the American College of Rheumatology, an ACR 20 response requires a patient to have a 20% reduction in the number of swollen and tender joints, together with a 20% improvement in three of five of the following criteria:  • Blood-borne inflammatory markers (CRP or ESR)  • Physician assessment  • Patient assessment  • Pain scale  • Disability/functional questionnaire"|"ACR criteria is indicated as ACR 20, ACR 50, ACR 70, and ACR 90."|http://www.hopkins-arthritis.org/physician-corner/education/acr/acr.html|Blank Created_By|Blank Updated_By.')
    
    
    
    SELECT old_values, a AS name, b AS deffinition, c, d, e, f, g
      FROM (
      SELECT Old_Values, CHAR((id+64)) AS col, value
        FROM @Audit_table
     CROSS APPLY dbo.splitter(Old_Values,'|')
      ) s
     PIVOT (
            MAX(value) FOR col IN (A,B,C,D,E,F,G)
    ) p

    You get columns, old_value, name, deffinition, c, d, e, f, g..

    You just need to map the other columns like this...


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by siddu_123 Tuesday, October 20, 2015 9:08 PM
    Tuesday, October 20, 2015 4:19 PM
  • Hello patrick,

    I did but the same output in the name,definition,notes ..... column I am getting NULL, I think it is the problem with my function which is not splitting the columns. do you have any other function which could split the columns.

    Thanks,

    Sidhu

    Tuesday, October 20, 2015 5:20 PM
  • Yes, the original function I proposed to you, which can be found here.

    You can use dbo.splitter to do this very quickly.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, October 21, 2015 4:53 PM