Split column and display in a new column in sql-CodePudding

I have a table like below (refer table 1) , which can have multiple ids. I want to split the Reviewer id and get the first name and last name from table 2 and display in table 1 column 2 and 3 using sql.

Table 1

| Reviewer id/s| Name 1 |Name 2|
| -------------| -------|------|
| 123; 124     | row    | row  |
| 126; 156     | row    | row  |

Table 2

| Reviewer id  | First Name |Last name |
| -------------| -----------|----------|
| 123          | Apple      | A        |
| 124          | Banana     | B        |
| 125          | Rose       | Rose     |
| 156          | Orange     | I        |
| 157          | Purple     | J        |

CodePudding user response：

Try this

select value,first_name,last_name from Tabel1 cross apply string_split(Reviewer_id,';') inner join Table2 on Tabel2.Reviewer_id=value

Test here SQL DEMO