problem
How to make left join to only teams displayed dynamically depend on fields exist on @selectedcolumncomma ?
i work on SQL server 2012 my problem how to generate join dynamically based on teams displayed without writing all
relations to all teams and my be result two teams only
on my situation below i make left join to 7 teams because may be one team from 7 come
so actually i need left join dynamically based on column generated dynamically
so if i one team displayed to lifecycle then one left join to lifecycle
so if i two team displayed to lifecycle and crosspart as my sample then two left join to lifecycle and crosspart
create table #tempVariables
(
DocumentId int
)
insert into #tempVariables(DocumentId) values (22490)
create table #Teams
(
TeamId int,
TeamName nvarchar(50),
ColumnName nvarchar(100)
)
insert into #teams
values
(1,'Package','Package'),
(2,'Parametric','Parametric'),
(3,'Scribing','Scribing'),
(4,'Lifecycle','Lifecycle'),
(5,'OBS','OBS'),
(6,'Cross','CrossPart'),
(7,'Rohs','Rohs')
create table #DocumentTeams
(
DocumentTeamId int identity(1,1),
DocumentId int,
TeamId nvarchar(50)
)
insert into #DocumentTeams(DocumentId,TeamId)
values
(22490,4),
(22490,6),
(22491,1),
(22491,5),
(22491,7)
Create table #FlowStatus
(
FlowStatusID int,
FlowStatus nvarchar(100)
)
insert into #FlowStatus
values
(1, 'Pending'),
(2, 'InProgress'),
(3, 'Done')
create table #DocumentPartTeams
(
DocumentPartTeamsId int,
PartId int,
DocumentId int,
Package int,
Parametric int,
Scribing int,
Lifecycle int,
OBS int,
CrossPart int,
Rohs int
)
insert into #DocumentPartTeams
(PartId,DocumentId,Package,Parametric,Scribing,Lifecycle,OBS,CrossPart,Rohs)
values
(1000,22490,null,null,null,1,null,1,null),
(1002,22490,null,null,null,1,null,1,null),
(1005,22491,2,null,null,null,2,null,2),
(1008,22491,2,null,null,null,2,null,1)
select dt.DocumentID,dt.TeamID, t.TeamName,t.ColumnName into #GetDocumentTeams from
#DocumentTeams dt
inner join #Teams t on t.TeamID=dt.TeamID
inner join #tempVariables tv on tv.DocumentId=dt.DocumentId
SELECT distinct ColumnName INTO #COLUMNS FROM #GetDocumentTeams
select t.TeamId,c.ColumnName into #indexedColumns from #COLUMNS c inner join pcn.Teams t
on t.ColumnName=c.ColumnName
declare @SeletColumnComma varchar(max)
select @SeletColumnComma = coalesce(@SeletColumnComma + ',','') + coalesce('fs' +cast
(teamid as nvarchar(20)) + '.FlowStatus as ' + ColumnName + 'Status','') from
#indexedColumns
select @SeletColumnComma
---------------
DECLARE @query nvarchar(max)
SET @query='
select distinct dpt.PartId,' + @SeletColumnComma + ' from #DocumentPartTeams dpt
inner join #GetDocumentTeams gdt on gdt.DocumentID=dpt.DocumentID
inner join #tempVariables tv on tv.DocumentId = dpt.DocumentId
left join #FlowStatus fs1 on dpt.Package=fs1.FlowStatusID
left join #FlowStatus fs2 on dpt.Parametric=fs2.FlowStatusID
left join #FlowStatus fs3 on dpt.Scribing=fs3.FlowStatusID
left join #FlowStatus fs4 on dpt.Lifecycle=fs4.FlowStatusID
left join #FlowStatus fs5 on dpt.OBS=fs5.FlowStatusID
left join #FlowStatus fs6 on dpt.CrossPart=fs6.FlowStatusID
left join #FlowStatus fs7 on dpt.Rohs=fs7.FlowStatusID
'
exec (@query)
drop table #teams
drop table #DocumentTeams
drop table #FlowStatus
drop table #DocumentPartTeams
drop table #COLUMNS
drop table #indexedColumns
drop table #tempVariables
drop table #GetDocumentTeams
Result displayed
PartId LifecycleStatus CrossPartStatus
1000 Pending Pending
1002 Pending Pending
so left join or inner join i needed to be only teams displayed dynamically
so how to make that please
