Pivot a Table in Framework Manager

[kennedto]

I am using Cognos 10.2.

I have a table that I want to pivot to make it available to report authors.

I have the SQL to do it and could use it to create a View.

But, I am thinking that adding this table as a Query Subject in Framework Manager might be more flexible.

The SQL is listed below.  What is the best way to turn this into a Query Subject?


select ticketid,
max( decode(assetattrid,'CRSTITLE',alnvalue) )as  Title,
max( decode(assetattrid,'STRTDATE',alnvalue) )as  StartDate,
max( decode(assetattrid,'ENDDATE',alnvalue) )as  EndDate,
max( decode(assetattrid,'SUPSCORE',numvalue) )as  SupScore,
max( decode(assetattrid,'ASSETSRC',alnvalue) )as  ASSETSRC,
max( decode(assetattrid,'BKSFILES',alnvalue) )as BKSFILES ,
max( decode(assetattrid,'BOOKNOTE',alnvalue) )as  BOOKNOTE,
max( decode(assetattrid,'CLRMDETS',alnvalue) )as CLRMDETS ,
max( decode(assetattrid,'CLSRMCNT',tablevalue) )as CLSRMCNT ,
max( decode(assetattrid,'COMMNOTE',alnvalue) )as COMMNOTE ,
max( decode(assetattrid,'COMMPKGS',alnvalue) )as COMMPKGS ,
max( decode(assetattrid,'CRSCODE',alnvalue) )as  CRSCODE,
max( decode(assetattrid,'CSRCLOC',alnvalue) )as CSRCLOC ,
max( decode(assetattrid,'DELEVTMG',tablevalue) )as DELEVTMG ,
max( decode(assetattrid,'DELINFO',alnvalue) )as DELINFO ,
max( decode(assetattrid,'DELSETCT',tablevalue) )as DELSETCT ,
max( decode(assetattrid,'ELMSREQ',numvalue) )as ELMSREQ ,
max( decode(assetattrid,'EVTREQSR',tablevalue) )as EVTREQSR ,
max( decode(assetattrid,'EXTINST',tablevalue) )as  EXTINST,
max( decode(assetattrid,'EVTREQTY',alnvalue) )as EVTREQTY ,
max( decode(assetattrid,'IMAGELOC',alnvalue) )as IMAGELOC ,
max( decode(assetattrid,'IMG_OS',alnvalue) )as IMG_OS ,
max( decode(assetattrid,'IMG_SW',alnvalue) )as IMG_SW ,
max( decode(assetattrid,'INTINSTR',alnvalue) )as INTINSTR ,
max( decode(assetattrid,'IRASCPU',numvalue) )as IRASCPU ,
max( decode(assetattrid,'IRASHD',numvalue) )as IRASHD ,
max( decode(assetattrid,'IRASRAM',numvalue) )as IRASRAM ,

max( decode(assetattrid,'SHPMATCT',tablevalue) )as SHPMATCT ,
max( decode(assetattrid,'STUDENTS',alnvalue) )as STUDENTS ,
max( decode(assetattrid,'TIMEZONE',alnvalue) )as TIMEZONE ,
max( decode(assetattrid,'VIVRECID ',numvalue) )as VIVRECID  ,
max( decode(assetattrid,'VPNIDS',alnvalue)  )as VPNIDS ,
max( decode(assetattrid,'VPNREQ',alnvalue)  )as VPNREQ
from maximo.ticketspec  where classstructureid='1048' group by ticketid;