CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Akansha
Akansha

Reputation: 209

Query not producing desired results

Below is the table structure of my table - group_table

enter image description here

The result I need should be as below

Below is the query I tried using. But not producing the result as desired.

SELECT * FROM 
(
SELECT A.PARENTID,A.NAME,A.CAPTION,A.PMKID,A.MKTPLANID,A.CATEGORY
FROM GROUP_TABLE A
)P
PIVOT
(
MAX(NAME)
FOR CAPTION IN ([Rollup],[Appeal Category],[PME])
)S

Upvotes: 2

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

This might be easier with conditional aggregation:

select mktplanid,
       max(case when caption = 'Rollup' then name end) as rollup,
       max(case when caption = 'Appeal Category' then name end) as appealcategory,
       max(case when caption = 'Planned Appeal' then name end) as plannedappeal,
       max(case when caption = 'PME' then name end) as PME,
       max(pmkid) as pmkid
from (select gt.*,
             row_number() over (partition by caption, mktplanid ORDER BY mktplanid) as seqnum 
      from group_table gt
     ) gt
group by mktplanid, seqnum;

Upvotes: 2

Related Questions