How to “Unpivot” or “pivot” Cognos SQL query

Check list of all posts

We can write query to “Unpivot” or “pivot” data. Sometimes we need to “Unpivot” or “pivot” data in Cognos as well, including simple list, crosstab and chart (need crosstab data to support.)  This document is intended to share an idea to resolve this generic issue. Please see sample below

Pivot Cognos SQL query

Query items in detail

Personal Accessories: case when [pivotFrom].[Product line] ='Personal Accessories' then [pivotFrom].[Revenue] else 0 end

Outdoor Protection: case when [pivotFrom].[Product line] ='Outdoor Protection' then [pivotFrom].[Revenue] else 0 end

Golf Equipment: case when [pivotFrom].[Product line] ='Golf Equipment' then [pivotFrom].[Revenue] else 0 end

Mountaineering Equipment:  case when [pivotFrom].[Product line] ='Mountaineering Equipment' then [pivotFrom].[Revenue] else 0 end

Camping Equipment:  case when [pivotFrom].[Product line] ='Camping Equipment' then [pivotFrom].[Revenue] else 0 end

Unpivot Cognos SQL query

Query items in detail for Rows

Rows query is to list all possibilities of product lines, which can be written in SQL based on different databases. ( see sample below with oracle database)

select 'Personal Accessories' as 'Product line', to_number(null) as Revenue from dual

union all

select 'Outdoor Protection' as 'Product line', to_number(null) as Revenue  from dual

union all

select 'Golf Equipment' as 'Product line', to_number(null) as Revenue  from dual

union all

select 'Mountaineering Equipment' as 'Product line', to_number(null) as Revenue  from dual

union all

select 'Camping Equipment' as 'Product line', to_number(null) as Revenue  from dual

The idea is to use an cross join, please see map below

Product line: [Rows].[Product line]

Revenue:

case

when [Rows].[Product line] = 'Personal Accessories' then [UnPivoteFrom].[Personal Accessories]

when [Rows].[Product line] = 'Outdoor Protection' then [UnPivoteFrom].[Outdoor Protection]

when [Rows].[Product line] = 'Golf Equipment' then [UnPivoteFrom].[Golf Equipment]

when [Rows].[Product line] = 'Mountaineering Equipment' then [UnPivoteFrom].[Mountaineering Equipment]

when [Rows].[Product line] = 'Camping Equipment' then [UnPivoteFrom].[Camping Equipment]

end