Use Select or PL SQL to Transpose

Question

I have a select query in MySQL shown below: This query executes and produces results in the form of a table

Current | Past1  | Past2  | Past 3  |  Past4
   200     600      800      000         88

I would like to transpose these results to get information in the form: Therefore I would like the reults to be (transposed)

Current    200
Past1      600
Past2      800
Past3      000
Past4       88

Can this be done using a SQL statement in oracle or do I need a PL SQL procedure for this. If a PL SQL can you provide some sample reference to it.

select
sum(case
    when CPT_ORIGINATED > SUBDATE(DATE(NOW()),30) THEN 1 else 0
    end) as Current,
sum(case
    when CPT_ORIGINATED > SUBDATE(DATE(NOW()),60) AND CPT_ORIGINATED < SUBDATE(DATE(NOW()),30) THEN 1 else 0
    end) as Past1,
sum(case
    when CPT_ORIGINATED > SUBDATE(DATE(NOW()),90) AND CPT_ORIGINATED < SUBDATE(DATE(NOW()),60) THEN 1 else 0
    end) as Past2,
sum(case
    when CPT_ORIGINATED > SUBDATE(DATE(NOW()),120) AND CPT_ORIGINATED < SUBDATE(DATE(NOW()),90) THEN 1 else 0
    end) as Past3,
sum(case
    when CPT_ORIGINATED > SUBDATE(DATE(NOW()),150) AND CPT_ORIGINATED < SUBDATE(DATE(NOW()),120) THEN 1 else 0
    end) as Past4
from `cpt-prod`
where CPT_STATE <> 'Closed'

Answer 1

One way to do that is a union:

WITH YourWith as (
    select value1, value2, value3, ...
    from YourTable
)
select name = 'Current', value1 from YourWith
union all
select name = 'Past1', value2 from YourWith
union all
select name = 'Past2', value3 from YourWith
union all
...

Answer 2

I'd do your query in a different way so that the data comes out the way you want.

1. Create a table (e.g. Ages) to define your intervals (name, lowerBound, upperBound)
   If you want get fancy, all you need is the "border values" and the corresponding upperBound can be constructed with a self-join.
2. Then use this table and join it to your "facts" table:

(Can't seem to figure out how to properly format the following statemen to show up as code without this line ...)

SELECT name, COUNT(*)
FROM cpt-prod JOIN Ages on cpt_originated 
    between Subdate(now - upperBound) And Subdate (now - lowerBound)
GROUP BY name

Depending on your needs and dbms, you may be able to define your Ages table in the actual SQL statement. (AFAIK, this can't be done in Oracle, but maybe someone can add a comment on how to do this in MySQL).