I have a table composed by :

and I want to have :

What I've tried

I've tried to cross apply :

SELECT 
   Id, Text, x.value as CalculatedJobId
FROM tbl_data
CROSS APPLY string_split(jobsIds, ',') x
WHERE x.value <> ''

But the case where the jobId is not included in JobsIds disapears, and the one where the jobsIds is empty disappears also. So I got :

I've tried to cross apply twice but it isn't a correct syntax :

SELECT 
   Id, Text, x.value as CalculatedJobId, y.value
FROM tbl_data
CROSS APPLY string_split(jobsIds, ',') x
CROSS APPLY JobId y
WHERE x.value <> ''

I've tried whith cross join but that's not the solution neither... I can't figure it out the solution, could you help me please ?

CodePudding user response：

Seems you need to concatenate the value of JobId first, and then split:

SELECT d.Id,
       d.Text,
       SS.value AS CalculatedJobId
FROM dbo.tbl_data d
     CROSS APPLY STRING_SPLIT(CONCAT_WS(',',d.JobID,d.jobsIds), ',') SS;

Note: this assumes that the value of JobsIds for Id 3 is NULL not a zero length string (''). If it is the latter, I would suggest you should be storing NULL first, but otherwise you could use NULLIF to convert it to NULL in the function.