Saturday, April 23, 2016

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

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

No comments:

Post a Comment