How to paste table-valued results together in an SQL query?-CodePudding

Imagine that I have a table named TEST with two columns named ID and CONTENT, and that the table has two rows, with the ID values 1 and 2, and the CONTENT values "abc,def,ghi" and "jkl,mno,pqr", that is, the table looks like this:

ID | CONTENT
1  | "abc,def,ghi"
2  | "jkl,mno,pqr"

Further imagine that I have a table-valued function SPLIT which splits each string into a table of its comma-separated components, i.e. SPLIT("abc,def,ghi") would return a table with one column and three rows containing the values "abc", "def" and "ghi", so that for example SELECT * FROM SPLIT("abc,def,ghi") would return the table:

COMPONENT
"abc"
"def"
"ghi"

How do I write an SQL query which returns the split strings of the CONTENT column adjoined to their corresponding ID values? That is, I would hope for the result to be:

ID | COMPONENT
1  | "abc"
1  | "def"
1  | "ghi"
2  | "jkl"
2  | "mno"
2  | "pqr"

I attempted to create an SQL fiddle as an example, but I'm not really much of an SQL professional and couldn't get it to work, so I hope that the above makes sense.

CodePudding user response：

if you have a table valued function SPLIT() then you can write a query like below in MS SQL Server

select id,component from test t outer apply SPLIT(t.content)