Friday, May 27, 2011

Rows to Columns - Scenario in SQL and Informatica

Scenario:
I Have a table - TEST_QUARTER and the data is

QUARTER SALES
Q1                1000
Q1                2000
Q1                3000
Q1                4000
Q2                5000
Q2                6000
Q2                7000
Q2                8000
Q3                1000
Q3                2000
Q3                3000
Q3                4000
Q4                5000
Q4                6000
Q4                7000
Q4                8000

and I need a target data as below

Q1          Q2          Q3           Q4
1000 5000 1000 5000
2000 6000 2000 6000
4000 8000 4000 8000
3000 7000 3000 7000

solution  for this scenario:
SELECT 
SUM(DECODE(QUARTER, 'Q1',SALES,0)) Q1,
SUM(DECODE(QUARTER, 'Q2',SALES,0)) Q2,
SUM(DECODE(QUARTER, 'Q3',SALES,0)) Q3,
SUM(DECODE(QUARTER, 'Q4',SALES,0)) Q4 
FROM 
(SELECT 
QUARTER, SALES,ROW_NUMBER() OVER (PARTITION BY QUARTER ORDER BY ROWNUM) SRL_NUM 
FROM TEST_QUARTER
ORDER BY QUARTER,SRL_NUM)
GROUP BY SRL_NUM 

4 comments:

  1. This article is nice if you want more about SQL just go through this link

    http://tekclasses.com/case-study-for-sql/

    ReplyDelete
  2. This article is nice if you want more about SQL just go through this link

    http://tekclasses.com/case-study-for-sql/

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete