Home > OS >  Insert Data By Splitting Column into Different Rows in SQL
Insert Data By Splitting Column into Different Rows in SQL

Time:06-16

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?

CodePudding user response:

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.

  • Related