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.