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