How can I split query result to create new table in MSSQL?

Question

I want to split the query result in MSSQL.

Example is below:

ID (PK)      BorrowID
1            1,3,4
2            4,5
3            2,3
4            1,6
5            2,7

I want to create new tables using upper table data.

NewID(PK)    ID      BorrowID
1            1       1
2            1       3
3            1       4
4            2       4
5            2       5

So I want to split the BorrowID Data to create new tables.

How can i get this?

I already try to use SUBSTRING, CHARINDEX function, but it doesn't well.

Is there a maximum number of commas and (ii) are you going to export the data only once? — Salman A, Mar 07 '19 at 12:03

score 1 · Accepted Answer · answered Mar 07 '19 at 12:10

You can try this to get individual values for your comma seperated list in MSSQL,

SELECT row_number() OVER (ORDER BY ID,BorrowID) as NewID,ID,BorrowID 
FROM ( SELECT A.*,
         Split.a.value('.', 'VARCHAR(100)') AS [BorrowID] 
     FROM  
     (
         SELECT ID, 
             CAST ('<M>' + REPLACE( [BorrowID], ',', '</M><M>') + '</M>' AS XML) AS Data  
          FROM [Table]
     ) AS A CROSS APPLY  [Data].nodes ('/M') AS Split(a))d;

score 1 · Answer 2 · answered Mar 07 '19 at 12:18

If you are on Sql Server 2016+ you may use String_split:

select ROW_NUMBER() over (order by t1.id, t2.borrowid) as newid, t1.id, t2.borrowid
 from t1 
  cross apply (select value as borrowid from string_split(t1.borrowid, ',')) as t2

Returns:

newid   id  borrowid
1   1   1
2   1   3
3   1   4
4   2   4
5   2   5
6   3   2
7   3   3

How can I split query result to create new table in MSSQL?

2 Answers2