2 Replies Latest reply: Feb 27, 2014 9:45 AM by Trey RSS

    Need help with NPD TeamMember query

    Trey

      I have this query and want to add another column with comma delimitd team membres for each project.

      But the subquery returns 1 to many team members. Anyone know how I can "pivot" the team members from the subquery into one comma delimited column?

       

      select distinct ProjectNumber, ProjectName, Description, TeamLeaders, StartDate, LaunchDate, StageNumber, StageName,

      NextStageName,

      fullBU, ProjectType, TeamMembers

      from

      (select p.pkid, p.ProjectNumber, pml.Title ProjectName, pml.description Description, p.TeamLeadersCaption TeamLeaders,

      1. p.OriginationDate StartDate,
      2. p.InMarketLaunchDate LaunchDate, p.stageNumber,
      3. ns.DefaultQuestionDocumentID StageName,

      (select DefaultQuestionDocumentID from npdStageMasterStages where StageNumber = (p.stageNumber + 1)) as NextStageName,

      (select u.FirstName + ' ' + u.LastName from npdprojects p, npdTeamMemberFormulationUsers tm

      inner join Users u on tm.fkUser = u.pkid

      where tm.fkProject = p.pkid) as TeamMembers,

      1. pt.Name projectType,
      2. ctax.Alias fullBU

      from npdProjects p

      inner join npdProjectML pml on p.pkid = pml.fkProject

      inner join npdProjectTypes pt on p.fkProjectType = pt.pkid

      inner join npdProjectBusinessUnitJoin buJoin on p.pkid = buJoin.fkProject

      inner join commonSMILTaxonomyNodeML ctax on bujoin.fkSMILTaxonomyNode = ctax.fkSMILTaxonomyNode

      inner join npdStageMasterStages ns on p.stageNumber = ns.StageNumber

      where

      --onhold = 0

      IsMarkedASDeleted = 0

      and p.stageNumber > 0 and p.stageNumber < 6

      ) as rawData

      group by projectnumber, projectname, description, TeamLeaders, startdate, launchdate, stagenumber, stagename,

             NextStageName,

             fullBU, ProjectType, TeamMembers

      order by fullBU, projectnumber

        • 1. Re: Need help with NPD TeamMember query
          Dmitriy Kolasnikov-Oracle

          Hi Trey,

           

          A common technique is to use FOR XML PATH in SQL server and XmlAgg in Oracle.

          In SQL server you can use something like this for your TeamMembers column:

          SELECT users.Firstname + ' ' + users.LastName + ','

                     FROM (SELECT DISTINCT u.FirstName as Firstname,  u.LastName as LastName FROM

                      npdTeamMemberFormulationUsers tm

                      inner join Users u on tm.fkUser = u.pkid

                      where tm.fkProject = p.pkid) as users

                      FOR XML PATH('') ) as TeamMembers,

           

          In Oracle it is going to be similar to this (didn't have a chance to verify it, but it is close):

          (

          select       

                 replace(

                        replace(

                        XmlAgg(

                               XmlElement("a", usrs.Firstname || ' ' || usrs.LastName )

                               order by

                               usrs.LastName )

                               .getClobVal(),

                 '<a>', ''),

                 '</a>',

                 ',')    

          from  (SELECT DISTINCT u.FirstName as Firstname,  u.LastName as LastName FROM

                      npdTeamMemberFormulationUsers tm

                      inner join Users u on tm.fkUser = u.pkid

                      where tm.fkProject = p.pkid) as usrs

          ) as TeamMembers,

           

           

          Another option to solve these types of problems is to create a SQL function that will accept a project PKID and return back a list of users by utilizing CURSORs

          In your database there is already a function called BuildNPDActivityTemplateStepUsernameList that accepts activity step PKID and returns a list of users in a comma separated list.

          You can use this function as a guide to create your own. Then you can call this function in your query.

           

          Dmitriy

          • 2. Re: Need help with NPD TeamMember query
            Trey

            DUDE YOU ROCK!!!!!!!!!!!!!! Sorry I forgot to say it was Sql Server.