CODEWITHSUNDEEP
sqloracle-databasepivotoracle9iflatten
user3084985
user3084985

Reputation: 41

Convert multiple rows into 1 column without pivot in oracle

I have a large sql query which joins columns from multiple tables to give data in this format: 

ID  CODE    Count   Action
-----------------------------
1   A       02      VIEWED
1   A       22      CLICKED
1   A       45      PRINTED
1   A       32      SCHEDULED
2   A       34      VIEWED
2   B       14      CLICKED
2   B       23      PRINTED
2   B       78      SCHEDULED
.
.
X   D       12      CLICKED

The value of action can be only VIEWED, CLICKED, SCHEDULED, PRINTED. Every combination of ID, CODE has set of max 4 rows. If there is no count for action say 'CLICKED', row for that is not present

So once I have the resultset, I want to flatten it to have data in this format : 

Id  CODE   VIEWED   CLICKED  PRINTED  SCHEDULED
-----------------------------------------------
1   A       02         11       45      32
1   B       54         57       89      45
2   B       34         14       23      78
3   G       null       null     28      20
X   D       null       12       null    null

My query does a lot of thing but in short it does: 

SELECT ID,CODE, ACTION, COUNT(*) AS COUNT  
FROM MY_TABLE 
GROUP BY ID,CODE,ACTION.

In this one query I want to get the result set AND then flatten the rows. I cant use union-alls etc to query the DB multiple times.

Database is Oracle 9i database, so pivot function will not work.

Upvotes: 1

Views: 8900

Answers (2)

Sai
Sai

Reputation: 689

 select id,code,decode(action,'VIEWED',count,'null') viewed,
                decode(action,'CLICKED',count,'null') Clicked, 
                decode(action,'PRINTED',count,'null') Printed,
                decode(action,'SCHEDULED',count,'null') Scheduled from you_table groub by ID,CODE;

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

Should be able to use SUM with CASE, removing ACTION from your GROUP BY clause:

SELECT ID, 
    CODE, 
    SUM(CASE WHEN ACTION = 'VIEWED' THEN 1 ELSE 0 END) Viewed, 
    SUM(CASE WHEN ACTION = 'CLICKED' THEN 1 ELSE 0 END) Clicked, 
    SUM(CASE WHEN ACTION = 'PRINTED' THEN 1 ELSE 0 END) Printed, 
    SUM(CASE WHEN ACTION = 'SCHEDULED' THEN 1 ELSE 0 END) Scheduled
FROM MY_TABLE 
GROUP BY ID,
    CODE

Upvotes: 1

Related Questions