I have below oracle table:
Table A
PID SEQ Desc
A 1 Have
A 2 a nice
A 3 day.
B 1 Nice Work.
C 1 Yes
C 2 we can
C 3 do
C 4 this work!
I want to aggregate Output of the table such that it should be -
PID Desc1 Desc2
A Have a nice day. Have
A Have a nice day. a nice
A Have a nice day. day!
B Nice Work. Nice Work.
C Yes we can do this work! Yes
C Yes we can do this work! we can
C Yes we can do this work! do
C Yes we can do this work! this work!
Notice that SEQ column determines order of desc aggregation. I have tried below query:
SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;
Which got me this result:
PID Desc
A Have a nice day.
B Nice Work.
C Yes we can do this work!
I am not sure how to get the desired result. Help!
Use LISTAGG as an analytic function rather than an aggregation function:
SELECT pid,
LISTAGG(descr, ' ') WITHIN GROUP (ORDER BY seq) OVER (PARTITION BY pid)
AS description,
descr
FROM table_a
Which, for the sample data:
CREATE TABLE Table_A (PID, SEQ, Descr) AS
SELECT 'A', 1, 'Have' FROM DUAL UNION ALL
SELECT 'A', 2, 'a nice' FROM DUAL UNION ALL
SELECT 'A', 3, 'day.' FROM DUAL UNION ALL
SELECT 'B', 1, 'Nice Work.' FROM DUAL UNION ALL
SELECT 'C', 1, 'Yes' FROM DUAL UNION ALL
SELECT 'C', 2, 'we can' FROM DUAL UNION ALL
SELECT 'C', 3, 'do' FROM DUAL UNION ALL
SELECT 'C', 4, 'this work!' FROM DUAL;
Outputs:
| PID | DESCRIPTION | DESCR |
|---|---|---|
| A | Have a nice day. | Have |
| A | Have a nice day. | a nice |
| A | Have a nice day. | day. |
| B | Nice Work. | Nice Work. |
| C | Yes we can do this work! | Yes |
| C | Yes we can do this work! | we can |
| C | Yes we can do this work! | do |
| C | Yes we can do this work! | this work! |