How to break a table CSVs into rows

Question

I have a table that look like this

ID  CustName CustAge  Attrib1      Attrib2
===========================================
1   Tom      23       A1,A3,H7,F4  H2,K9,L2
2   Mike     34       Y2,K1,P7     A4,S9
3   Mary     62       R6,W3        K6,S6,L8
4   John     49       W4,K4,W2,G2  E2,N5
5   Ali      36       W3,R5,H2     E3
6   Mark     22       A2           H9,Q7,T6

I want to break it into rows

so it look like this

ID  CustName CustAge  Attrib1      AttribNo
===========================================
1   Tom      23       A1           1
1   Tom      23       A3           1
1   Tom      23       H7           1
1   Tom      23       F4           1
1   Tom      23       H2           2
1   Tom      23       K9           2
1   Tom      23       L2           2
2   Mike     34       Y2           1
2   Mike     34       K1           1
2   Mike     34       P7           1
2   Mike     34       A4           2
2   Mike     34       S9           2

I tried this but did not work

SELECT *, (SELECT Value FROM STRING_SPLIT(Attrib1, ',')) FROM Customers

I also tried

SELECT *  
FROM Customers C 
INNER JOIN (SELECT Value FROM STRING_SPLIT(C.Attrib1, ',')) A1

still not good

Any idea how to do that?

Fix your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes it is.). — sticky bit, Apr 17 '21 at 18:28
@stickybit: That is what he is trying to fix ..... (if I read/understand the question) — Luuk, Apr 17 '21 at 18:28
@Luuk: To me it seems like they just want a query to split the data but leave the original data, which is the actual problem, untouched... — sticky bit, Apr 17 '21 at 18:30
@stickybit: OK point taken, now let's hope that `asmgx` is really going for the correct solution... — Luuk, Apr 17 '21 at 18:36

eshirvana · Accepted Answer · 2021-04-17T18:34:45.613

4

you can use cross apply:

select * from tablename t
cross apply STRING_SPLIT(t.Attrib1,',') ss

and union would be easiest way to get your final result:

select *,1 AttribNo from tablename t
cross apply STRING_SPLIT(t.Attrib1,',') s1
union all 
select *,2 AttribNo from tablename t
cross apply STRING_SPLIT(t.Attrib2,',') s2

edited Apr 17 '21 at 18:34

answered Apr 17 '21 at 17:56

eshirvana

23,227
3
22
38

`union all` is querying the table twice which is unnecessary – Charlieface Apr 17 '21 at 22:26

Gordon Linoff · Answer 2 · 2021-04-17T18:40:37.087

4

I would do this with a single apply:

select t.id, t.custno, t.custage, a.*
from t cross apply
     (select s.value as attrib, 1 as attribno
      from string_split(t.attrib1, ',') s
      union all
      select s.value, 2
      from string_split(t.attrib2, ',') s
     ) a;

edited Apr 17 '21 at 18:40

answered Apr 17 '21 at 18:21

Gordon Linoff

1,242,037
58
646
786

1

The eliminated `union all` version seems to be incorrect (wrong value for `attribno`) – Luuk Apr 17 '21 at 18:34

How to break a table CSVs into rows

2 Answers2