How to extract Database Column Data comma based?

Question

How to extract data from a comma-separated column?

This is my database table which have ID and data two columns.

ID      Data
-------------------
1       1, 2,3...50

I'm looking something like if any comma recognize in Data column then it will separate that value and make column like below

ID     1    2    3
-------------------
1      1    2    3

How can I make it with T-SQL or normal query?

Are these permanent tables or are they @ or # and what version of sql server? — Matt, Jan 03 '14 at 16:19
there is an example - http://stackoverflow.com/questions/3575971/mysql-split-column-into-two — I kiet, Jan 03 '14 at 16:27
[Click Here](http://sqlbisam.blogspot.com/2013/12/extract-data-from-delimited-column.html) for a function which splits the data based on delimiter — sam, Jan 03 '14 at 16:45
Read these asap http://en.wikipedia.org/wiki/Database_normalization and http://en.wikipedia.org/wiki/Third_normal_form — Mike B, Jan 03 '14 at 20:14

M.Ali · Answer 1 · 2014-01-03T20:11:17.777

Test Data

DECLARE @t TABLE (Data NVARCHAR(1000))

INSERT INTO @t
VALUES 
('1,2,3,4,5'),
('6,7,8,9,10'),
('A,B,C,D,E')

Query

;WITH Split_Fields (Field, xmlfields)
AS
(
    SELECT Data AS Field,
    CONVERT(XML,'<Fields><field>'  
    + REPLACE(Data,',', '</field><field>') + '</field></Fields>') AS xmlfields
      FROM @t
)

 SELECT       
 xmlfields.value('/Fields[1]/field[1]','varchar(100)') AS Column1,    
 xmlfields.value('/Fields[1]/field[2]','varchar(100)') AS Column2,
 xmlfields.value('/Fields[1]/field[3]','varchar(100)') AS Column3,    
 xmlfields.value('/Fields[1]/field[4]','varchar(100)') AS Column4,
 xmlfields.value('/Fields[1]/field[5]','varchar(100)') AS Column5
 FROM Split_Fields

Result Set

╔═════════╦═════════╦═════════╦═════════╦═════════╗
║ Column1 ║ Column2 ║ Column3 ║ Column4 ║ Column5 ║
╠═════════╬═════════╬═════════╬═════════╬═════════╣
║ 1       ║ 2       ║ 3       ║ 4       ║ 5       ║
║ 6       ║ 7       ║ 8       ║ 9       ║ 10      ║
║ A       ║ B       ║ C       ║ D       ║ E       ║
╚═════════╩═════════╩═════════╩═════════╩═════════╝

EDIT

After you have asked me to explain further how you would get data into column I dont know how much more I can explain it. But Here is a demonstration on your data, I dont know how to changed you ID from -1 to P00000000001, anyway this is how you would go about doing it with your data.

Query

DECLARE @t TABLE (DATA NVARCHAR(4000))
INSERT INTO @t
VALUES
('-1,H0000001,2011-02-19 00:00:00,I - D,GOA,INDIA ')

;WITH Split_Fields (Field, xmlfields)
AS
(
    SELECT Data AS Field,
    CONVERT(XML,'<Fields><field>'  
    + REPLACE(Data,',', '</field><field>') + '</field></Fields>') AS xmlfields
      FROM @t
)

 SELECT       
 xmlfields.value('/Fields[1]/field[1]','NVARCHAR(MAX)') AS Column1,    
 xmlfields.value('/Fields[1]/field[2]','NVARCHAR(MAX)') AS ID,
 xmlfields.value('/Fields[1]/field[3]','NVARCHAR(MAX)') AS [Date],    
 xmlfields.value('/Fields[1]/field[4]','NVARCHAR(MAX)') AS Div,
 xmlfields.value('/Fields[1]/field[5]','NVARCHAR(MAX)') AS [State],
 xmlfields.value('/Fields[1]/field[6]','NVARCHAR(MAX)') AS [Country]
 FROM Split_Fields

Result

╔═════════╦══════════╦═════════════════════╦═══════╦═══════╦═════════╗
║ Column1 ║    ID    ║        Date         ║  Div  ║ State ║ Country ║
╠═════════╬══════════╬═════════════════════╬═══════╬═══════╬═════════╣
║      -1 ║ H0000001 ║ 2011-02-19 00:00:00 ║ I - D ║ GOA   ║ INDIA   ║
╚═════════╩══════════╩═════════════════════╩═══════╩═══════╩═════════╝

Actually can we make something like in while loop till we get comma it will iterate and make a new column and with value? — Hemant, Jan 03 '14 at 17:48
hmmmm using a while loop means iterating through one string multiple times, on each iteration reducing the size of string, then getting the value and inserting into a different column everytime, isnt it a bit over kill of a fairly simple query shown above :) — M.Ali, Jan 03 '14 at 18:00
First of all thanks for reply but One more thing in your given query here you have given predefined values INSERT INTO @t VALUES ('1,2,3,4,5'), ('6,7,8,9,10'), ('A,B,C,D,E') and in my senario values will be somewhere in query like select data * from table 1 here in data would be my values which contains two many values with comma which I need to separate it with any column name and those values are not scalar. — Hemant, Jan 03 '14 at 19:39
Can you show some sample data ?? as long as values are separated with `,` this query should work fine. — M.Ali, Jan 03 '14 at 19:46
Sure Query : - select data from sheet1 will get data like this in query Data:-1,H0000001,2011-02-19 00:00:00,I - D,GOA,INDIA and here I want something like 1 | ID | Date | Div | State | Country 1 | P00000000001 | 2011-02-19 00:00:00 | I-D | Goa | INDIA — Hemant, Jan 03 '14 at 19:51

score 0 · Answer 2 · edited May 23 '17 at 12:03

I've outlined a method using the UNPIVOT operator, which parses the CSV into rows and then unpivots the rows into positional columns. It is of course limited to the number of positional columns defined in the unpivot outline.

To do this without limits you will need to use dynamic sql to build your statement. Check out this question for an example.

declare @a table (ID int, Data varchar(100));
insert into @a
    select 1, '1,2,3' union all
    select 2, '1,4' union all
    select 3, '1,5,X,,,,Y' union all
    select 4, '123';

declare @Numbers table (n int primary key); --use your own number table
insert into @Numbers
    select 1 union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9 union all 
    select 10;


select pvt.*
from    (
            select Id, d.Position, d.Value
            from @a
            cross
            apply   (   select  row_number()over(order by n),
                                substring(',' + Data + ',', n + 1, charindex(',', ',' + Data + ',', n + 1) - n -1)
                        from    @Numbers
                        where   n <= len(',' + Data + ',') - 1 and substring(',' + Data + ',', n, 1) = ','
                    )d(Position, Value)
        )s(Id, Position, Value)
pivot   (max(Value) for Position in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]))pvt;

Am I missing something? What CSV? And the other guy is messing around with XML. Am I reading the wrong question?? — Mike B, Jan 03 '14 at 20:17

How to extract Database Column Data comma based?

2 Answers2