8 Replies - 694 Views - Last Post: 08 January 2018 - 11:39 AM

#1 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 338
  • Joined: 20-May 16

how to unpivot oracle select results?

Posted 08 January 2018 - 10:10 AM

how to unpivot oracle select results?

I am using oracle sql.
below you will find a query and its result. i uploaded as much i could, so you can get an idea.

Query
Select DISTINCT 
GREATEST(0, ...)  as col1, 
GREATEST(0, ...) as col2, 
SUM(b1) as col3, 
SUM(b2) as col4,
SUM(CASE WHEN ...) as col5
From mainTable m
left join 
        (
        Select DISTINCT 
        MAX(...) as a1 
        from mainTable m
        left join ...
        where ...
        )...
left join 
        (
        Select DISTINCT 
        MAX(Case WHEN ...) as b1,
        MAX(Case WHEN ...) as b2,
        MAX(Case WHEN ...) as b3,
        MAX(Case WHEN ...) as b4
        from mainTable m
        left join ...
        left join ...
        left join ...
        where ...
        )...
left join ...
left join ...
WHERE ... 


Query Results
|------|------|------|-----|
| col1 | col2 | col3 | ... |
|------|------|------|-----|
| 12   | 2    | 56   | ... |
|------|------|------|-----| 



But I want the following results:
|------|------|
| col1 | 12   |
|------|------|
| col2 | 2    | 
|------|------|
| col3 | 56   |
|------|------|
| ...  | ...  | 
|------|------|


so i took my orginal query and put it in sub select statment and added pivot tag in end.
error: ORA-00907: missing right parenthesis
line: pivot ((myname, Total_Counts) FOR Category IN (

select * from
(
Select DISTINCT 
GREATEST(0, ...)  as col1, 
GREATEST(0, ...) as col2, 
SUM(b1) as col3, 
SUM(b2) as col4,
SUM(CASE WHEN ...) as col5
From mainTable m
left join 
        (
        Select DISTINCT 
        MAX(...) as a1 
        from mainTable m
        left join ...
        where ...
        )...
left join 
        (
        Select DISTINCT 
        MAX(Case WHEN ...) as b1,
        MAX(Case WHEN ...) as b2,
        MAX(Case WHEN ...) as b3,
        MAX(Case WHEN ...) as b4
        from mainTable m
        left join ...
        left join ...
        left join ...
        where ...
        )...
left join ...
left join ...
WHERE ... 
)
pivot ((myname, Total_Counts) FOR Category IN ( 
(
   col1 AS 'col1',
   col2 AS 'col2',
   col3 AS 'col3',
   col4 AS 'col4',
   col5 AS 'col5'
   )
);

This post has been edited by ikhlas06: 08 January 2018 - 10:18 AM


Is This A Good Question/Topic? 0
  • +

Replies To: how to unpivot oracle select results?

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,808
  • Joined: 12-June 08

Re: how to unpivot oracle select results?

Posted 08 January 2018 - 10:13 AM

"unpivot" ? Do you mean just 'pivot'? Where was it pivoted before?
Was This Post Helpful? -1
  • +
  • -

#3 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 338
  • Joined: 20-May 16

Re: how to unpivot oracle select results?

Posted 08 January 2018 - 10:18 AM

no it was not pivot before. I guess i do need to pivot first
Was This Post Helpful? 0
  • +
  • -

#4 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 338
  • Joined: 20-May 16

Re: how to unpivot oracle select results?

Posted 08 January 2018 - 10:18 AM

no it was not pivot before. I guess i do need to pivot first. i have edited my post above

This post has been edited by ikhlas06: 08 January 2018 - 10:19 AM

Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,808
  • Joined: 12-June 08

Re: how to unpivot oracle select results?

Posted 08 January 2018 - 10:20 AM

'Pivot first'? You mean just do a pivot? Is there a second step you are looking to do that I am not seeing?
Was This Post Helpful? -1
  • +
  • -

#6 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 338
  • Joined: 20-May 16

Re: how to unpivot oracle select results?

Posted 08 January 2018 - 10:32 AM

yes.. i need to just Pivot

error: ORA-00907: missing right parenthesis
line: pivot ((myname, Total_Counts) FOR Category IN (

This post has been edited by ikhlas06: 08 January 2018 - 10:33 AM

Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,808
  • Joined: 12-June 08

Re: how to unpivot oracle select results?

Posted 08 January 2018 - 10:37 AM

... be careful about editing things _AFTER_ folk have replied.

Basic things to look at.. if it cannot find the right ) then check there first.. then work backwards. Is there a formatting issue that would confuse the interpreter? Are variables defined like 'Total_Counts'?
Was This Post Helpful? 1
  • +
  • -

#8 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 338
  • Joined: 20-May 16

Re: how to unpivot oracle select results?

Posted 08 January 2018 - 11:23 AM

View Postmodi123_1, on 08 January 2018 - 10:37 AM, said:

... be careful about editing things _AFTER_ folk have replied.

Basic things to look at.. if it cannot find the right ) then check there first.. then work backwards. Is there a formatting issue that would confuse the interpreter? Are variables defined like 'Total_Counts'?




BTW #2 modi123_1, every thing you said was wrong and you put me on wrong track....

turn out you did had to use 'unpivot' even though its not pivoted before.

below is a sample code if any one needs it. hope it helps

with temp as (
(
Select DISTINCT 
GREATEST(0, ...)  as col1, 
GREATEST(0, ...) as col2, 
SUM(b1) as col3, 
SUM(b2) as col4,
SUM(CASE WHEN ...) as col5
From mainTable m
left join 
        (
        Select DISTINCT 
        MAX(...) as a1 
        from mainTable m
        left join ...
        where ...
        )...
left join 
        (
        Select DISTINCT 
        MAX(Case WHEN ...) as b1,
        MAX(Case WHEN ...) as b2,
        MAX(Case WHEN ...) as b3,
        MAX(Case WHEN ...) as b4
        from mainTable m
        left join ...
        left join ...
        left join ...
        where ...
        )...
left join ...
left join ...
WHERE ... 
)
select * from temp
unpivot 
(
value for myname in (col1, col2, col3, col4, col5)
)


Was This Post Helpful? 0
  • +
  • -

#9 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,808
  • Joined: 12-June 08

Re: how to unpivot oracle select results?

Posted 08 January 2018 - 11:39 AM

Aint that a kick in the pants when someone is trying to susse out what another means. Since, you know, one typically calls an 'unpivot' after a 'pivot'... you know, bringing a pivoted table back to a pre-pivot state. I guess I didn't do a thorough enough job getting all the facts in first.

So it goes.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1