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

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  March 28, 2020 at 8:41 pm

  #3738659

  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
  
  PartIdLifecycleStatusCrossPartStatus
  1000Pending          Pending
  1002Pending          Pending
  so left join or inner join i needed to be only teams displayed dynamically 
  
  so how to make that please

• Steve Collins

  SSCrazy

  Points: 2157

  More actions

  March 29, 2020 at 8:19 pm

  #3738745

  This sql gives me 'deja view' because the OP has posted many questions on SSC.  ahmed_elbarbary.2010 please recognize the answers in some way.  I posted an answer a few months ago and never heard anything back.  Maybe it was correct, maybe it missed by a mile, I have no idea...

  https://www.sqlservercentral.com/forums/topic/how-to-update-status-with-conflict-data-where-chemical-temp-table-have-same-chem

  Regarding the latest sql, it appears there could've possibly been a premature pivot.  The #DocumentPartTeams table could be un-pivoted, joined to #FlowStatus on FlowStatusID where TeamName (ColumnName) is not null, and then re-pivoted.  However, if you have a table (or could create a query) with these 4 columns: DocumentId, TeamId, PartId, FlowStatusID, it could be summarized and pivoted.  It should be possible to do without dynamic sql if the list of teams is fixed.

   

   

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können