Hello,
I have 3 columns in my test table like
NAME | DATE | TOTAL_SALES
---------- ----------------------------
TOM | 01/12/2020 | 8
TOM | 02/12/2020 | 9
TOM | 03/12/2020 | 4
GEORGE | 01/12/2020 | 6
GEORGE | 02/12/2020 | 5
is there any way to merge column 2 and 3 based on column 1 into one single row. And the result should be like
NAME | 01/12/2020 | 02/12/2020 | 03/12/2020 |
-------- --------------- -------------- --------------
TOM | 8 | 9 | 4 |
GEORGE | 6 | 5 | 0 |
You can PIVOT!
with rws as (
select mod ( level, 2 ) val,
date'2020-12-01' + mod ( level, 3 ) dt
from dual
connect by level <= 5
), formatted as (
select val, to_char ( dt, 'yyyy-mm-dd' ) dt
from rws
)
select * from formatted
pivot (
count (*) for dt in (
'2020-12-01' as d1,
'2020-12-02' as d2,
'2020-12-03' as d3
)
);
VAL D1 D2 D3
1 1 1 1
0 0 1 1 Pivoting date values into column heading is tricky because:
* You need to convert them into character values first before using them in the IN clause
* They're likely to change each time, meaning you need a dynamic PIVOT. There's no simple solution for this.
For more on this, read:
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot