query output changes using sql

Question

How to convert the sql output in below format for the following table?

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

Expected format

Date            A_SPACE     B_SPACE     
---             ---             ---         
2018-03-05      5534            34324   
2018-03-06      4645            435

Search (in the documentation, on this site, or on the web generally) for 'pivot'. — Alex Poole, Apr 27 '18 at 14:13

score 1 · Answer 1 · answered Apr 27 '18 at 14:35

1

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.

answered Apr 27 '18 at 14:35

Gordon Linoff

1,242,037
58
646
786

1

We have more columns like a_space, b_space, c_space and so on... which can be pulled from a different query. Wondering if we can automate it with pivot instead manually writing `sum statment` – user1595858 Apr 27 '18 at 15:09
@user1595858 . . . Google "Oracle dynamic pivot". – Gordon Linoff Apr 28 '18 at 22:41

DxTx · Answer 2 · 2018-04-29T07:42:12.250

0

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

edited Apr 29 '18 at 07:42

answered Apr 28 '18 at 16:03

DxTx

3,049
3
23
34

query output changes using sql

Table

Expected format

2 Answers2