How to convert the sql output in below format for the following table?
Date(col 1) Name(col 2) Value(col 3))
2018-03-05 A_SPACE 5534
2018-03-05 B_SPACE 34324
2018-03-06 A_SPACE 4645
2018-03-06 B_SPACE 435
Date A_SPACE B_SPACE
--- --- ---
2018-03-05 5534 34324
2018-03-06 4645 435
If you have a table, the simplest way is probably conditional aggregation:
select date,
sum(case when name = 'A_SPACE' then value else 0 end) as a_space,
sum(case when name = 'B_SPACE' then value else 0 end) as b_space
from t
group by date
order by date;
If your table is the result of a query, then you can probably incorporate similar logic into the query.
sum statment
Try this.
SELECT *
FROM tablename PIVOT( Max(value) FOR NAME IN('A_SPACE','B_SPACE'))
ORDER BY dates;
Demo: http://www.sqlfiddle.com/#!4/d97ee/23/0
If you need a dynamic pivot, refer these articles.
https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
Dynamic pivot in oracle sql
dynamic columns in oracle using sql
