i have rather complicated pivot query perhaps ought broken parts. problem end duplicate rows need rid of.
select postname, activityid, projectname, projectid, objectiveid, objectivename, lastname, firstname, volid, orgs, coms, isnull([males 0-9], 0) [males 0-9], isnull([males 10-17], 0) [males 10-17], isnull([males 18-24], 0) [males 18-24], isnull([males 25+], 0) [males 25+], isnull([females 0-9], 0) [females 0-9], isnull([females 10-17], 0) [females 10-17], isnull([females 18-24], 0) [females 18-24], isnull([females 25+], 0) [females 25+] ( select a.postname, a.activityid, pr.postid, pr.projectname, pr.projectid, o.objectiveid, o.objectivename, a.lastname, a.firstname, a.volid, isnull(count(distinct ac1.activitycollaboratorid), 0) orgs, isnull(count(distinct ac2.activitycollaboratorid), 0) coms, p.addind, b.name [pcvrt].[dbo].activityobjectives ao left join [pcvrt].[dbo].[reportcsppview] on ao.activityid = a.activityid left join [pcvrt].[dbo].[activityparticipants] p on a.activityid = p.activityid , a.groupname not null , a.mmed <> 1 full outer join [pcvrt].[dbo].buckets b on b.bucketid = p.bucketid , b.bucketsetid = ( select bucketsetid [pcvrt].[dbo].bucketsets name = 'standard' ) left join pcvrt.dbo.objectives o on o.objectiveid = ao.objectiveid left join pcvrt.dbo.activitycollaborators ac1 on a.activityid = ac1.activityid , ac1.collaboratortypeid = 1 left join pcvrt.dbo.activitycollaborators ac2 on a.activityid = ac2.activityid , ac2.collaboratortypeid = 2 left join pcvrt.dbo.projects pr on pr.projectid = a.projectid group a.activityid, a.postname, pr.postid, pr.projectname, pr.projectid, o.objectiveid, o.objectivename, a.lastname, a.firstname, a.volid, p.addind, b.name ) source pivot ( max(addind) name in([males 0-9], [males 10-17], [males 18-24], [males 25+], [females 0-9], [females 10-17], [females 18-24], [females 25+]) ) pvt projectname not null , objectivename not null order projectid, objectiveid, projectname, objectivename, lastname, firstname so, result of so:
+----------+--------------------------------------+---------------------------------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------------------------------------+----------+-----------+-----------+------+------+----------+------------+------------+----------+------------+--------------+--------------+------------+ | postname | activityid | projectname | projectid | objectiveid | objectivename | lastname | firstname | volid | orgs | coms | males0-9 | males10-17 | males18-24 | males25+ | females0-9 | females10-17 | females18-24 | females25+ | +----------+--------------------------------------+---------------------------------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------------------------------------+----------+-----------+-----------+------+------+----------+------------+------------+----------+------------+--------------+--------------+------------+ | zomba | 1432bb61-6dc7-4eb9-b7f1-08d3bd1500ed | youth in development thematic | 663e5793-d40d-4543-aa63-287af32567e6 | 1fd82dec-a8c3-4679-9ca2-66e8977b6a30 | community engagement in english | amadou | daniel | 100582935 | 1 | 1 | 15 | 22 | 12 | 0 | 32 | 22 | 12 | 0 | | zomba | 1432bb61-6dc7-4eb9-b7f1-08d3bd1500ed | youth in development thematic | 663e5793-d40d-4543-aa63-287af32567e6 | 1e4edf72-a13a-4bcb-9c78-6a879f512a44 | community engagement in literacy | amadou | daniel | 100582935 | 1 | 1 | 15 | 22 | 12 | 0 | 32 | 22 | 12 | 0 | | zomba | 1432bb61-6dc7-4eb9-b7f1-08d3bd1500ed | youth in development thematic | 663e5793-d40d-4543-aa63-287af32567e6 | accaf29c-c2c8-488e-9406-6be5ff6d1961 | promoting gender equitable practices | amadou | daniel | 100582935 | 1 | 1 | 15 | 22 | 12 | 0 | 32 | 22 | 12 | 0 | | zomba | 1432bb61-6dc7-4eb9-b7f1-08d3bd1500ed | youth in development thematic | 663e5793-d40d-4543-aa63-287af32567e6 | 819966d1-1d0e-4889-ae2f-72e0cdacf850 | student-friendly schools | amadou | daniel | 100582935 | 1 | 1 | 15 | 22 | 12 | 0 | 32 | 22 | 12 | 0 | | zomba | 1cc95367-d9ee-46ed-981e-74d3e944bbe8 | project test - youth in development thematic | 543e1bf9-e17b-47af-ae06-4c14ee494ba6 | e36000fc-bae1-492b-823e-6f3441083727 | increasing life skills , health education children , youth | allen | touoze | 100343614 | 1 | 1 | 0 | 22 | 15 | 12 | 0 | 26 | 13 | 8 | | zomba | 3913846e-3973-4404-b3ca-d3ecfe357c00 | project test - youth in development thematic | 543e1bf9-e17b-47af-ae06-4c14ee494ba6 | e36000fc-bae1-492b-823e-6f3441083727 | increasing life skills , health education children , youth | allen | touoze | 100343614 | 1 | 2 | 0 | 2 | 0 | 0 | 0 | 12 | 2 | 0 | | zomba | 1432bb61-6dc7-4eb9-b7f1-08d3bd1500ed | secondary / community activities - education sector framework | 06c877d7-317b-432c-b038-a548ac7bb59f | 1fd82dec-a8c3-4679-9ca2-66e8977b6a30 | community engagement in english | amadou | daniel | 100582935 | 1 | 1 | 15 | 22 | 12 | 0 | 32 | 22 | 12 | 0 | | zomba | 1432bb61-6dc7-4eb9-b7f1-08d3bd1500ed | secondary / community activities - education sector framework | 06c877d7-317b-432c-b038-a548ac7bb59f | 1e4edf72-a13a-4bcb-9c78-6a879f512a44 | community engagement in literacy | amadou | daniel | 100582935 | 1 | 1 | 15 | 22 | 12 | 0 | 32 | 22 | 12 | 0 | | zomba | 1432bb61-6dc7-4eb9-b7f1-08d3bd1500ed | secondary / community activities - education sector framework | 06c877d7-317b-432c-b038-a548ac7bb59f | accaf29c-c2c8-488e-9406-6be5ff6d1961 | promoting gender equitable practices | amadou | daniel | 100582935 | 1 | 1 | 15 | 22 | 12 | 0 | 32 | 22 | 12 | 0 | | zomba | 1432bb61-6dc7-4eb9-b7f1-08d3bd1500ed | secondary / community activities - education sector framework | 06c877d7-317b-432c-b038-a548ac7bb59f | 819966d1-1d0e-4889-ae2f-72e0cdacf850 | student-friendly schools | amadou | daniel | 100582935 | 1 | 1 | 15 | 22 | 12 | 0 | 32 | 22 | 12 | 0 | +----------+--------------------------------------+---------------------------------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------------------------------------+----------+-----------+-----------+------+------+----------+------------+------------+----------+------------+--------------+--------------+------------+ so, need 1 row per project->objective pairing, rolling activities.
thoughts?
you did not provide want final result, based on current result make few guesses problem is.
when applying pivot function, should include columns need display in final result because columns in select list not part of aggregate function or pivot columns grouped.
your current data has multiple values objectiveid column when include column, have multiple rows displayed in final result.
i guessing should use similar following:
select postname, activityid, projectname, projectid, objectivename, lastname, firstname, volid, orgs, coms, isnull([males 0-9], 0) [males 0-9], isnull([males 10-17], 0) [males 10-17], isnull([males 18-24], 0) [males 18-24], isnull([males 25+], 0) [males 25+], isnull([females 0-9], 0) [females 0-9], isnull([females 10-17], 0) [females 10-17], isnull([females 18-24], 0) [females 18-24], isnull([females 25+], 0) [females 25+] ( select a.postname, a.activityid, pr.postid, pr.projectname, pr.projectid, o.objectivename, a.lastname, a.firstname, a.volid, isnull(count(distinct ac1.activitycollaboratorid), 0) orgs, isnull(count(distinct ac2.activitycollaboratorid), 0) coms, p.addind, b.name [pcvrt].[dbo].activityobjectives ao left join [pcvrt].[dbo].[reportcsppview] on ao.activityid = a.activityid left join [pcvrt].[dbo].[activityparticipants] p on a.activityid = p.activityid , a.groupname not null , a.mmed <> 1 full outer join [pcvrt].[dbo].buckets b on b.bucketid = p.bucketid , b.bucketsetid = ( select bucketsetid [pcvrt].[dbo].bucketsets name = 'standard' ) left join pcvrt.dbo.objectives o on o.objectiveid = ao.objectiveid left join pcvrt.dbo.activitycollaborators ac1 on a.activityid = ac1.activityid , ac1.collaboratortypeid = 1 left join pcvrt.dbo.activitycollaborators ac2 on a.activityid = ac2.activityid , ac2.collaboratortypeid = 2 left join pcvrt.dbo.projects pr on pr.projectid = a.projectid group a.activityid, a.postname, pr.postid, pr.projectname, pr.projectid, o.objectivename, a.lastname, a.firstname, a.volid, p.addind, b.name ) source pivot ( max(addind) name in([males 0-9], [males 10-17], [males 18-24], [males 25+], [females 0-9], [females 10-17], [females 18-24], [females 25+]) ) pvt projectname not null , objectivename not null order projectid, projectname, objectivename, lastname, firstname 
Comments
Post a Comment