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
Set @IDs = @IDs + ','
Declare @Pos1 Int
Declare @Pos2 Int
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)
Set @Pos2=@Pos1+1
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