Split one column into multiple columns using SQL Query

Question

I want to Split one column into multiple columns using SQL Query

data not only select it is store to other column

i have this type of table:

 [ID]    [DATE_TIME]           [VALUE]

 1       2014-09-24 11:59:00   Record No = 00> 40 >Record No = 01> 40 >Record No = 02> 71>

I need to Split Record number wise like this:

 [ID]    [DATE_TIME]          [VALUE1]              [VALUE2]    

 1       2014-09-24 11:59:00  Record No = 00> 40 >  Record No = 01> 40 >

 [VALUE3]                [VALUE4]
 Record No = 02> 71>     NULL

Also, give sample data for your desired result that correlates to your initial data. Post the code you have tried. — SS_DBA
Here is a great place to start. spaghettidba.com/2015/04/24/… — Sean Lange
All I can say is you have a horrible database design here. Storing multiple pieces of information in a single tuple violates 1NF. In addition you are storing the column name right alongside the values. This is just awful and is a serious pain to deal with. You are going to have to split this not once, but twice. Here is my personal favorite splitter. sqlservercentral.com/articles/Tally+Table/72993 There are several other excellent options here. sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange

Kashif Qureshi Kashif Qureshi · Accepted Answer · 2017-12-05T15:08:08

You can use xml and pivot to achive this. Try this:

SELECT * 
FROM   (SELECT A.[ID], 
               'VALUE' 
               + Cast(Row_number() OVER(partition BY [ID] ORDER BY ID ) AS 
               VARCHAR(4)) 
                      AS rowid, 
               A.[DATE_TIME], 
               A.[VALUE], 
               split.a.value('.', 'VARCHAR(100)') 
                      AS [VALUES] 
        FROM   (SELECT [ID], 
                       [DATE_TIME], 
                       [VALUE], 
                       Cast ('<M>' 
                             + Replace([VALUE], '>Record No', '</M><M>Record No' 
                             ) 
                             + '</M>' AS XML) AS String 
                FROM   #temp) AS A 
               CROSS apply string.nodes ('/M') AS Split(a)) SourceTable 
       PIVOT ( Max([VALUES]) 
             FOR ROWID IN([VALUE1], 
                          [VALUE2], 
                          [VALUE3],
                          [VALUE4]) ) AS pivottable

Dynamically you can use following script:

CREATE TABLE #temp 
  ( 
     [ID]        INT IDENTITY(1, 1), 
     [DATE_TIME] DATETIME, 
     [VALUE]     VARCHAR(255) 
  ) 

INSERT INTO #temp 
            (DATE_TIME, 
             VALUE) 
VALUES     ('2014-09-24 11:59:00', 
            'Record No = 00> 40 >Record No = 01> 40 >Record No = 02> 71>') 

SELECT A.[ID], 
       'VALUE' 
       + Cast(Row_number() OVER(partition BY [ID] ORDER BY ID ) AS VARCHAR(4)) 
       AS rowid, 
       A.[DATE_TIME], 
       A.[VALUE], 
       split.a.value('.', 'VARCHAR(100)') 
       AS [VALUEs] 
INTO   #temp1 
FROM   (SELECT [ID], 
               [DATE_TIME], 
               [VALUE], 
               Cast ('<M>' 
                     + Replace([VALUE], '>Record No', '</M><M>Record No') 
                     + '</M>' AS XML) AS String 
        FROM   #temp) AS A 
       CROSS apply string.nodes ('/M') AS Split(a) 

DECLARE @cols  AS NVARCHAR(max), 
        @query AS NVARCHAR(max) 

SET @cols = Stuff((SELECT DISTINCT ',' + Quotename(c.ROWID) 
                   FROM   #temp1 c 
                   FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '' 
            ) 

SET @query = ' select *
                from
                (
                    select * 
                    from #temp1
                ) SourceTable
                pivot
                (
                    max([VALUEs])
                    for rowid in(' + @cols + ')
                ) as PivotTable'

EXECUTE(@query)

Split one column into multiple columns using SQL Query

1 Answers