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

You can do it with a single SQL statement (you can add your "case" logic to the template I have below, I just wanted to show you the basic idea, which is to use the union).

CREATE TABLE results (cur NUMBER, past1 number, past2 NUMBER, past3 NUMBER, past4 NUMBER);
INSERT INTO results VALUES(200,600,800,0,88);

SELECT NAME, val
FROM 
(SELECT 'current' NAME, cur val FROM results)
UNION ALL
(SELECT 'past1' NAME, past1 val FROM results)
UNION ALL
(SELECT 'past2' NAME, past2 val FROM results)
UNION ALL
(SELECT 'past3' NAME, past3 val FROM results)
UNION ALL
(SELECT 'past4' NAME, past4 val FROM results)

Answer 2

How about something like this:

select 'Current' As TimePeriod,
sum(case when CPT_ORIGINATED > SUBDATE(DATE(NOW()),30) THEN 1 else 0
    end) as CountOfThings,
from `cpt-prod`
where CPT_STATE <> 'Closed'
UNION ALL
SELECT 'Past1' AS TimePeriod,
sum(case when CPT_ORIGINATED > SUBDATE(DATE(NOW()),60) AND CPT_ORIGINATED < SUBDATE(DATE(NOW()),30) THEN 1 else     0
    end) as CountOfThings,
from `cpt-prod`
where CPT_STATE <> 'Closed'

and so on for each row?