Insert Data By Splitting Column into Different Rows in SQL

I have a temporary table Temporary Table Image

ID	Code	Type
1	A001	A, B
2	B001	A, B
3	C001	A, B

& a source table in my database Source Table Image

ID	Code	Text
1	A001	Text A
2	B001	Text B
3	C001	Text C

I want to insert the the ‘type’ data to have output like this. Data types of ‘type’ is char(20). Output Table Image

ID	Code	Text	Type
1	A001	Text A	A
2	A001	Text A	B
3	B001	Text B	A
4	B001	Text B	B
5	C001	Text C	A
6	C001	Text C	B

Is it possible to come out the output by using pure SQL query?

Answer

You can get the desired result using ROW_NUMBER for the id column and STRING_SPLIT to separate the values from “A,B” into A and B.

SELECT ROW_NUMBER() OVER (ORDER BY t1.code) AS id,
t1.code, t2.text, sp.value AS type 
FROM table1 AS t1 
OUTER APPLY STRING_SPLIT(t1.type, ',') sp
JOIN table2 AS t2 
ON t1.code = t2.code;

You can replicate this here: db<>fiddle

I recommend to rename the columns “type” and “text” if possible since SQL key words should not be used as column name or table name.