Pivot SQL Example

[Binway]

Hi All,
Others may find this helpful.  I had a requirement to include some fields and summary values in a report that seemed to work best in a pivot table.  But rather than a crosstab report it would need to be pivot sql for a few reasons.  I couldn't see any suggestions on here other than it can be done and may have to use a pass-through sql query.  So I have done a bit of a test using the GOSALESEDW sql server database and it's package in 10.2.2.
If you run this sql in your cognos report it will give you a feel for the data.

Code Select Expand

select [POSITION_CODE3],sum([MAX_SALARY]) as MaxSal
  from [GOSALESDW].[gosalesdw].[EMP_POSITION_DIM]
  group by position_code3

Now to change this to a Pivot of some of the values in the Position_Code3 field I constructed this SQL.  I didn't need to change from Native to Pass-Through but other versions or setups may require this change.

Code Select Expand

  Select 'mysal' as my_sal_calc, [300],[320],[330],[340]
  FROM (
  Select [POSITION_CODE3],[MAX_SALARY] from [GOSALESDW].[gosalesdw].[EMP_POSITION_DIM]) as srctbl
  PIVOT ( MAX([MAX_SALARY]) for [POSITION_CODE3] in ([300],[320],[330],[340])) as pvt

When you run this in Cognos the data from the top piece of sql is pivoted
my_sal_calc   300   320   330   340
mysal   180,000   180,000   180,000   180,000

A very simple example I know that obviously has flaws if the data changes in the Code3 field, certainly can be built on but thought this may be useful for others.
Binway

[BigChris]

Thanks for sharing that Binway...might come in handy at some point