Splitting data from one column into multiple columns & selecting data from multiple tables

See more:

SQL

Hi Guys,

So I have data which is held in one column and i want to split the data using delimiter of space. I have achieved this but I want to be able to select other data which I will not need to split into the results as a reporting.

For example,

Table 1: Employees details

Table 2: Payroll

Table 3: Departments

Now there is an ID that links all these tables which is the same. But i was to be able to select data from all these tables and also be able to do the splitting using the delimiter. Below is what I have done so far:

Here is the select statement I had before the split, now i want to split only column in one table but link all tables together and display results which i did using this statement:

select distinct f.fle_id,
				f.filename,
				c.clp_clip_name,
				f.FileExtension,
				c.locationsList, 
				f.UNC,
				f.Directory,
				f.framerate		 
from			lls_clips C with (NOLOCK)
inner join		lls_fileimports F with (NOLOCK)
on				c.fle_id = f.fle_id 
inner join		lls_jobs J with (NOLOCK)
on				c.fle_id = j.fle_id
where				J.LastUpdated >= dateadd(day, -1, getdate()) 

What I have tried:

declare @delimiter Varchar(50)

Set @delimiter = ' '
;WITH CTE As
(
	select 
			[LocationsList],
			[clp_clip_name],
			CAST('<m>' + Replace([LocationsList], @delimiter , '</m><m>') + '</m>' AS XML)
			AS [LocationsList XML]
		From [lls_clips]
)
Select	distinct

		[clp_clip_name],
		[locationsList], 		
		[LocationsList XML].value('/M[1]', 'varchar(50)') As [First Location],
		[LocationsList XML].value('/M[2]', 'varchar(50)') As [Second Location],
		[LocationsList XML].value('/M[3]', 'varchar(50)') As [Third Location],
		[LocationsList XML].value('/M[4]', 'varchar(50)') As [Fourth Location],
		[LocationsList XML].value('/M[5]', 'varchar(50)') As [Firth Location],
		[LocationsList XML].value('/M[6]', 'varchar(50)') As [Sixth Location]

From CTE
GO

******NOTES BELOW******

--Here are the other tables i want to link with [Files table - Files F] and [Jobs J]
--the fields from Files include - F.FL_ID, f.filename, f.uncpath, f.direcotry
--the fields from Jobs include J.FL_ID, J.filename
--the fields from the above table lls_clips include C.FL_ID, C.filename and so on

--So i need to be able to link all that data together in one report. The only column i am splitting is in one table which is LLS_Clips table