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

Need help with NPD TeamMember query

Trey Explorer
Currently Being Moderated

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 Explorer
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points