Before Oracle 11g it was a bit of a hassle, after 11g, it was very simple. Now has the function business is, has a test table records is the document approval information, the ID is the approval information primary key, SHEET_ID is the foreign key, is the document Id,remark for approves the content, on the front-end list page, wants to see this document all approval information, must display in a lattice inside.
sql>drop table test Purge;
sql>CREATE TABLE Test
(
ID Number (10),
SHEET_ID Number (10),
Remark VARCHAR2 (50)
);
sql>INSERT into test values (1,100, ' approve comments 1 ');
sql>INSERT into test values (2,100, ' approve comments 2 ');
sql>INSERT into test values (3,100, ' approve comments 3 ');
sql>INSERT into test values (4,200, ' agree 1 ');
sql>INSERT into test values (5,200, ' agree 2 ');
sql>INSERT into test values (6,200, ' agree 3 ');
sql>INSERT into test values (7,300, ' fallback 1 ');
sql>INSERT into test values (8,300, ' fallback 2 ');
sql>Commit
sql> Col c_remark format A40;
Sql> Select Sheet_id,listagg (Remark, ', ') within GROUP (order by ID) as C_remark
From Test
Group BY SHEET_ID;
sheet_id C_remark
---------- ----------------------------------------
100 Approval of Comments 1, approval of opinion 2, approval of views 3
200 Agree 1, agree 2, agree 3
300 fallback 1, fallback 2
There is another way to do this:
Sql> Select sheet_id, Listagg (remark, ', ') within
GROUP (
Order by ID)-Over (partition by sheet_id) C_remark
From Test;
sheet_id C_remark
---------- ----------------------------------------
100 Approval of Comments 1, approval of opinion 2, approval of views 3
100 Approval of Comments 1, approval of opinion 2, approval of views 3
100 Approval of Comments 1, approval of opinion 2, approval of views 3
200 Agree 1, agree 2, agree 3
200 Agree 1, agree 2, agree 3
200 Agree 1, agree 2, agree 3
300 fallback 1, fallback 2
300 fallback 1, fallback 2
Sql> SELECT DISTINCT sheet_id, Listagg (remark, ', ') within
GROUP (
Order by ID)-Over (partition by sheet_id) C_remark
From Test;
sheet_id C_remark
---------- ----------------------------------------
200 Agree 1, agree 2, agree 3
100 Approval of Comments 1, approval of opinion 2, approval of views 3
300 fallback 1, fallback 2
Oracle 11g Column Career Listagg