Resolved: Insert Data By Splitting Column into Different Rows in SQL

Question:

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.

If you have better answer, please add a comment about this, thank you!

Resolved: Insert Data By Splitting Column into Different Rows in SQL

Resolved: Time Complexity: Find the number of people who know a secret leetcode 2327

Resolved: Best way to create 3d matrix of variables in PULP

Resolved: Openlayers: Update clusters depending on filter (geometryFunction)

Leave A Reply