How can I take out each element of string to a separate column?

Question

I have table like:

|----|----------------|--------------------------|----------------------|
| id |     tickets    |      comb1               |    comb2             |
|---------------------|--------------------------|----------------------|
|  1 | 3146000011086..|  ,13,  ,31,  ,50,66,77,..|  ,22,38,40,  ,  ..   |                       
|---------------------|--------------------------|----------------------|
|2..n| 314600001924...| 5,14,23,  ,  ,50,  ,  ,..| 4,12,21,  ,47,  ,..  |
|-----------------------------------------------------------------------|

I need to take out each elements of comb1 and comb2 to columns like:

|---------------------|------------------|------------------|---------------|
|   val_of_comb1(1)   |  val_of_comb1(2) | ..val_of_comb2(1)|val_of_comb2(2)|
|---------------------|------------------|------------------|---------------|
|                     |         13       |                  |      22       |
|---------------------|------------------|------------------|---------------|
|          5          |         14       |     .. 4         |      12       |
|---------------------|------------------|------------------|---------------|

Maybe take out each element with loop? (but if I have a lot of records how it will affect the database) welcome any ideas

Depending on the version of SQL Server you are using you could use STRING_SPLIT — cte6, Nov 05 '19 at 04:41
If you use pl/sql and push it to db server to manipulate the db that would be the most performant method as this would require minimum data transfer. Handling this by using for loop in the application development code would be easier if you are not good in sql but it is indeed an option. The approach will depend whether you want this change to be persisted in the database itself or it's just required at application code level. Regarding exact solution, please refer: https://stackoverflow.com/questions/29147009/sql-server-comma-separated-column-to-multiple-columns — amandeep1991, Nov 05 '19 at 04:45

tymtam · Answer 1 · 2019-11-05T06:09:42.877

A. `cross apply`, `pivot`, and `string_split`

Here is a version if Comb1 splits into 12 strings.

drop table X
create table X 
(
id int,
comb1 nvarchar(max)
);

insert into X values (1,',13,  ,31,  ,50,66,77,..');
insert into X values (2,'5,14,23,  ,  ,50,  ,  ,..');

-- From https://stackoverflow.com/questions/12195504/splitting-a-string-then-pivoting-result by Kannan Kandasamy
select * from (
select * from X x cross apply (select RowN=Row_Number() over (Order by (SELECT NULL)), value from string_split(x.Comb1, ',') ) d) src
pivot (max(value) for src.RowN in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as p

id  comb1   1   2   3   4   5   6   7   8   9   10  11  12
1   ,13,  ,31,  ,50,66,77,..        13      31      50  66  77  ..  NULL    NULL    NULL
2   5,14,23,  ,  ,50,  ,  ,..   5   14  23          50          ..  NULL    NULL    NULL

B. Just STRING_SPLIT and code

One option is to use STRING_SPLIT which will return rows.

select value from STRING_SPLIT(',13,  ,31,  ,50,66,77,..',',');

value

13

31

50
66
77
..

You could then collect all the rows in your code and collect them as an array.

Thank you sir, exactly what i need! @tymtam – Abba Bold Nov 05 '19 at 10:43 — Abba Bold, Nov 05 '19 at 10:43

How can I take out each element of string to a separate column?

1 Answers1

A. cross apply, pivot, and string_split

B. Just STRING_SPLIT and code

A. `cross apply`, `pivot`, and `string_split`