将数据从一列拆分成多列&从多个表中选择数据 [英] Splitting data from one column into multiple columns & selecting data from multiple tables

问题描述

大家好，



所以我的数据保存在一列中，我希望使用空格分隔符来分割数据。我已经实现了这一点，但我希望能够选择其他数据，我不需要将其作为报告分成结果。



例如，



表1：员工详情



表2：工资单



表3：部门



现在有一个链接所有这些表的ID相同。但我能够从所有这些表中选择数据，并且能够使用分隔符进行拆分。下面是我到目前为止所做的：



这是我在拆分之前的select语句，现在我想在一个表中只拆分列但链接所有表一起显示我使用此声明所做的结果：

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

******以下说明



- 以下是我想要与[文件表 - 文件F]和[作业J]链接的其他表格。
- 文件中的字段包括 - F.FL_ID，f.filename，f.uncpath， f.direcotry

- 来自Jobs的字段包括J.FL_ID，J.filename

- 上表中的字段lls_clips包括C.FL_ID，C.filename



- 所以我需要能够在一份报告中将所有数据链接在一起。我拆分的唯一一列是在一个表中，即LLS_Clips表

******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

推荐答案

我在表student中有一个像'david＃jones'这样的全名列。



所以如果你想将这些名字分隔为david和姓氏为jones

这里的查询就像



选择POSITION（'＃'IN FULLNAME）AS POS，SUBSTR（FULLNAME，1，POS-1）AS FNAME，SUBSTR（FULLNAME，POS + 1）AS LNAME

来自学生



列POS标识特殊字符的邮件，

下一个子字符串是从第一个字符串开始的字符到最后一个但是作为FNAME的特殊字符之一，从特殊字符到结尾的下一个位置被视为LNAME。

I have a column with fullname like 'david#jones' in table student.

so if you want to separate these like firstname as "david" and lastname as "jones"
here goes the query like

SELECT POSITION( '#' IN FULLNAME) AS POS ,SUBSTR(FULLNAME , 1,POS-1) AS FNAME,SUBSTR(FULLNAME,POS+1) AS LNAME
FROM STUDENT

The column POS identifies the postition of the special character,
the next sub string is taken form the first character to the last but one of the special character as FNAME and from the next position of the special character to the end is taken as LNAME.

这篇关于将数据从一列拆分成多列&从多个表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！