CODEWITHSUNDEEP
sqlsql-servert-sql
asmgx
asmgx

Reputation: 8014

How to break a table CSVs into rows

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?

Upvotes: 1

Views: 89

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

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;

Upvotes: 4

eshirvana
eshirvana

Reputation: 24593

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

Upvotes: 4

Related Questions