SQL查询(将csv id更改为csv名称) [英] SQL query (Change csv id's to csv name's)
问题描述
我有这样的查询。
选择pid,从table1 deptid;
其中deptid是varchar,值类似于(1,3,4)
现在我有另一张桌子作为部门(deptid( int,PK),DeptName)
我想要而不是从精选中获取deptid我可以在csv.like中获得deptNames
pid | deptname
1 | dept1,dept4,dept7
2 | dept4,dept9
应该采取什么措施。
谢谢,
首先创建此函数:
使用 DataBase_Name
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
创建 功能 [dbo]。[fnSplitter](< span class =code-sdkkeyword> @ IDs Varchar ( 4000 ))
返回 @ Tbl_IDs 表(ID nvarchar ( 100 ) COLLATE SQL_Latin1_General_CP1_CS_AS)
作为
开始
- 附加逗号
设置 @ID = @ID + ' ,'
- 保持搜索位置的索引
声明 @ Pos1 Int
声明 @ Pos2 Int
-从第一个字符开始
设置 @Pos1 = 1
设置 @Pos2 = 1
@ Pos1< len( @ids 跨度>)GT;
开始
设置 @ Pos1 = CharIndex(' ,', @ ID , @ Pos1 )
插入 @Tbl_IDs 选择子串( @ID , @ Pos2 ,@ Pos1- @ Pos2)
- 转到下一个非逗号字符
设置 @Pos2 = @Pos1 + 1
- 从下一个charcater搜索
设置 @ Pos1 = @ Pos1 + 1
结束
返回
结束
添加函数后使用此查询:
选择 t.PId,Stuff((选择 ' ,' + DeptName
From DeptTable
其中 DeptId 在(选择 ID 来自 fnSplitter(t.DeptId))
对于 XML路径(< span class =code-string>' '), TYPE )
.value(' 。',' VARCHAR(max)'), 1 , 1 ,' ')[部门名称]
来自 表 t
测试代码:
创建表#Temp
(
PId int,
DeptId VarChar(40 )COLLATE SQL_Latin1_General_CP1_CS_AS
)
创建表#Dept
(
DeptId VarChar(40)COLLATE SQL_Latin1_General_CP1_CS_AS,
DeptName VarChar(40)COLLATE SQL_Latin1_General_CP1_CS_AS
)
插入#Temp
选择1,'1,2,3'联盟所有
选择2,'4,5'
插入#Dept
选择'1','Dept1'联盟所有
选择'2','Dept2'联盟所有
选择'3','Dept3'Union所有
选择'4','Dept4'联盟所有
选择'5','Dept5'
选择t.PId,Stuff((选择','+ DeptName
来自#Dept
哪个部门Id In(从fnSplitter(t.DeptId)中选择ID)
对于XML路径(''),TYPE)
.value('。','VARCHAR(max)'),1,1, '')[DeptName]
来自#Temp t
掉落表#Temp
掉落表#Dept
输出:
PId DeptName
--- ---------
1 Dept1,Dept2,Dept3
2 Dept4,Dept5
创建表#Emp
(
PId int,
DeptId VarChar(40)
)
创建表#Dept
(
DeptId VarChar(40)
DeptName VarChar(40)
)
插入#Emp
选择1,'1,2,3'联盟所有
选择2,'4,5'
插入#部门
选择'1','部门1'联盟全部
选择'2','部门2'联盟所有
选择'3','部门3'联盟全部
选择'4 ','Dept4'Union All
选择'5','Dept5'
SELECT pid,Depts = STUFF((SELECT','+ d.DeptName
FROM #Dept AS d
INNER JOIN #Emp AS ei
ON','+ ei.deptid +','LIKE'%,'+ CONVERT(VARCHAR(12),d.deptid)+' ,%'
WHERE ei.pid = e.pid
ORDER BY DeptName
FOR XML PATH,TYPE).value('。[1]','nvarchar(max)'), 1,1,'')
FROM #Emp AS e
ORDER BY pid;
Drop Table #Emp
Drop Table #Dept
您可以使用以下方法。
*您创建的函数将返回与给定pid关联的逗号分隔的部门名称。
< pre lang =sql> CREATE FUNCTION dbo.GetCommaSepDepartments( @pid int )
RETURNS varchar ( 1000 )
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ temp VARCHAR 跨度>;
SET @ temp =( SELECT SUBSTRING(
( SELECT ' , ' + D.DeptName
FROM 表1 s
INNER < span class =code-keyword> JOIN 部门D s.deptid = D.Deptid
where s.pid = tpid
ORDER BY D.DeptName
FOR XML PATH(' ')
), 2 , 200000 )
);
RETURN ( @ temp );
END ;
*之后您可以使用以下查询以获得最终结果
< pre lang = sql >
从table1 T1中选择T1.pid,GetCommaSepDepartments(@tpid int)
;
< / pre > 跨度>
Hi,
I have a query like this.
select pid,deptid from table1;
in which deptid is varchar and value like (1,3,4)
Now I have another table as department(deptid(int,PK), DeptName)
I want instead of get deptid from select I can get deptNames in csv.like
pid | deptname
1 | dept1,dept4,dept7
2 | dept4,dept9
What should be approach for this.
Thanks,
First Create this Function:
Use DataBase_Name GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create Function [dbo].[fnSplitter] (@IDs Varchar(4000) ) Returns @Tbl_IDs Table (ID nvarchar(100) COLLATE SQL_Latin1_General_CP1_CS_AS ) As Begin -- Append comma Set @IDs = @IDs + ',' -- Indexes to keep the position of searching Declare @Pos1 Int Declare @Pos2 Int -- Start from first character Set @Pos1=1 Set @Pos2=1 While @Pos1<len(@ids)> Begin Set @Pos1 = CharIndex(',',@IDs,@Pos1) Insert @Tbl_IDs Select Substring(@IDs,@Pos2,@Pos1-@Pos2) -- Go to next non comma character Set @Pos2=@Pos1+1 -- Search from the next charcater Set @Pos1 = @Pos1+1 End Return End
After Adding the Function Use this Query:
Select t.PId,Stuff((Select ','+DeptName From DeptTable Where DeptId In (Select ID From fnSplitter(t.DeptId)) For XML Path(''), TYPE) .value('.','VARCHAR(max)'), 1, 1, '') [DeptName] From Table t
Tested Code:
Create Table #Temp ( PId int, DeptId VarChar(40) COLLATE SQL_Latin1_General_CP1_CS_AS ) Create Table #Dept ( DeptId VarChar(40) COLLATE SQL_Latin1_General_CP1_CS_AS, DeptName VarChar(40) COLLATE SQL_Latin1_General_CP1_CS_AS ) Insert into #Temp Select 1,'1,2,3' Union all Select 2,'4,5' Insert into #Dept Select '1','Dept1' Union All Select '2','Dept2' Union All Select '3','Dept3' Union All Select '4','Dept4' Union All Select '5','Dept5' Select t.PId,Stuff((Select ','+DeptName From #Dept Where DeptId In ( Select ID From fnSplitter(t.DeptId)) For XML Path(''), TYPE) .value('.','VARCHAR(max)'), 1, 1, '') [DeptName] From #Temp t Drop Table #Temp Drop Table #Dept
Output:
PId DeptName --- --------- 1 Dept1,Dept2,Dept3 2 Dept4,Dept5
Create Table #Emp ( PId int, DeptId VarChar(40) ) Create Table #Dept ( DeptId VarChar(40) DeptName VarChar(40) ) Insert into #Emp Select 1,'1,2,3' Union all Select 2,'4,5' Insert into #Dept Select '1','Dept1' Union All Select '2','Dept2' Union All Select '3','Dept3' Union All Select '4','Dept4' Union All Select '5','Dept5' SELECT pid, Depts = STUFF((SELECT ',' + d.DeptName FROM #Dept AS d INNER JOIN #Emp AS ei ON ',' + ei.deptid + ',' LIKE '%,' + CONVERT(VARCHAR(12), d.deptid) + ',%' WHERE ei.pid = e.pid ORDER BY DeptName FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') FROM #Emp AS e ORDER BY pid; Drop Table #Emp Drop Table #Dept
Well you can use following approach.
* You create a function that will return comma separated department names associated with a given pid.
CREATE FUNCTION dbo.GetCommaSepDepartments(@pid int) RETURNS varchar(1000) WITH EXECUTE AS CALLER AS BEGIN DECLARE @temp varchar; SET @temp = (SELECT SUBSTRING( (SELECT ',' + D.DeptName FROM Table1 s INNER JOIN Department D on s.deptid = D.Deptid where s.pid = tpid ORDER BY D.DeptName FOR XML PATH('') ),2,200000) ); RETURN(@temp); END;
* After this you can use below query to get the final result
<pre lang="sql"> select T1.pid, GetCommaSepDepartments(@tpid int) from table1 T1; </pre>
这篇关于SQL查询(将csv id更改为csv名称)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!