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

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2686

    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
  • scdecade

    SSC Eights!

    Points: 807

    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.

     

     

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply