feature: LIST_AGG

[BohuTANG]

Summary

Syntax

Aggregate function

LIST_AGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]

Window function

LIST_AGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]
    OVER ( [ PARTITION BY <expr2> ] )

Example

CREATE TABLE orders (
    o_orderstatus VARCHAR,
    o_clerk VARCHAR,
    o_totalprice DECIMAL(10, 2)
);

INSERT INTO orders (o_orderstatus, o_clerk, o_totalprice) VALUES
('F', 'Clerk#0001', 550000.00),
('O', 'Clerk#0002', 600000.00),
('F', 'Clerk#0003', 530000.00),
('O', 'Clerk#0004', 540000.00),
('P', 'Clerk#0005', 560000.00),
('F', 'Clerk#0006', 450000.00), 
('P', 'Clerk#0007', 700000.00),
('O', 'Clerk#0008', 800000.00),
('F', 'Clerk#0009', 520000.01);

SELECT
    o_orderstatus,
    LIST_AGG(o_clerk, ', ') WITHIN GROUP (ORDER BY o_totalprice DESC) AS clerks_list
FROM
    orders
WHERE
    o_totalprice > 520000
GROUP BY
    o_orderstatus;