How to make join to teams display without join all teams?


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 

create table #DocumentTeams
DocumentTeamId int identity(1,1),
DocumentId int,
TeamId nvarchar(50)
insert into #DocumentTeams(DocumentId,TeamId) 

Create table #FlowStatus
FlowStatusID int,
FlowStatus  nvarchar(100)
insert into #FlowStatus 
(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


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 

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


hope this helps .... :slight_smile: