Select statement with a dynamic group by clause-CodePudding

How possible to group records conditionally? For example I have records with FLOW_NUMBER column, values 1, 2, 3. When FLOW_NUMBER value is 1, I want to group this records, but when FLOW_NUMBER is 2 or 3, I don't want to group.

SELECT
    LISTAGG(DISTINCT PRODORD, ',')  PRODORD,
    SUM(QTY) AS QTY
FROM (
    SELECT
        1 AS FLOW_NUMBER,
        'A' AS PRODORD,
        1 AS QTY
    FROM DUAL
    UNION ALL
    SELECT
        1 AS FLOW_NUMBER,
        'B' AS PRODORD,
        1 AS QTY
    FROM DUAL
    UNION ALL
    SELECT
        1 AS FLOW_NUMBER,
        'C' AS PRODORD,
        1 AS QTY
    FROM DUAL

    UNION ALL
    SELECT
        2 AS FLOW_NUMBER,
        'A' AS PRODORD,
        1 AS QTY
    FROM DUAL
    UNION ALL
    SELECT
        2 AS FLOW_NUMBER,
        'B' AS PRODORD,
        1 AS QTY
    FROM DUAL
    UNION ALL
    SELECT
        2 AS FLOW_NUMBER,
        'C' AS PRODORD,
        1 AS QTY
    FROM DUAL

    UNION ALL
    SELECT
        3 AS FLOW_NUMBER,
        'A' AS PRODORD,
        1 AS QTY
    FROM DUAL
    UNION ALL
    SELECT
        3 AS FLOW_NUMBER,
        'B' AS PRODORD,
        1 AS QTY
    FROM DUAL
    UNION ALL
    SELECT
        3 AS FLOW_NUMBER,
        'C' AS PRODORD,
        1 AS QTY
    FROM DUAL
)
GROUP BY FLOW_NUMBER -- bad group by clause

There is example photo of group by which I need.

CodePudding user response：

I don't think there is anyway to achieve it except using 2 query combined with UNION ALL clause -

SELECT FLOW_NUMBER, LISTAGG(DISTINCT PRODORD, ',')  PRODORD,
    SUM(QTY) AS QTY
FROM temp
  WHERE FLOW_NUMBER = 1
GROUP BY FLOW_NUMBER
  UNION ALL
SELECT FLOW_NUMBER, PRODORD,
       QTY
FROM temp
  WHERE FLOW_NUMBER <> 1;

Demo.

CodePudding user response：

You can try this:

select
substr(fn,1,decode(instr(fn,','),0,1000,instr(fn,','))-1) flow_number,
prodord, qty
from (SELECT
    case when FLOW_NUMBER=1 then to_char(flow_number) else  to_char(flow_number)||','||rownum end fn,
    LISTAGG(DISTINCT PRODORD, ',')  PRODORD,
    SUM(QTY) AS QTY
FROM (
    SELECT
        1 AS FLOW_NUMBER,
        'A' AS PRODORD,
        1 AS QTY
    FROM DUAL
    UNION ALL
    SELECT
        1 AS FLOW_NUMBER,
        'B' AS PRODORD,
        1 AS QTY
    FROM DUAL
    UNION ALL
    SELECT
        1 AS FLOW_NUMBER,
        'C' AS PRODORD,
        1 AS QTY
    FROM DUAL

    UNION ALL
    SELECT
        2 AS FLOW_NUMBER,
        'A' AS PRODORD,
        1 AS QTY
    FROM DUAL
    UNION ALL
    SELECT
        2 AS FLOW_NUMBER,
        'B' AS PRODORD,
        1 AS QTY
    FROM DUAL
    UNION ALL
    SELECT
        2 AS FLOW_NUMBER,
        'C' AS PRODORD,
        1 AS QTY
    FROM DUAL

    UNION ALL
    SELECT
        3 AS FLOW_NUMBER,
        'A' AS PRODORD,
        1 AS QTY
    FROM DUAL
    UNION ALL
    SELECT
        3 AS FLOW_NUMBER,
        'B' AS PRODORD,
        1 AS QTY
    FROM DUAL
    UNION ALL
    SELECT
        3 AS FLOW_NUMBER,
        'C' AS PRODORD,
        1 AS QTY
    FROM DUAL
)
GROUP BY case when FLOW_NUMBER=1 then to_char(flow_number) else  to_char(flow_number)||','||rownum end);

I used rownum here, but you can probably replace it by other things (e.g. rowid, if the data is from a table, just be sure to have a unique value)

CodePudding user response：

SELECT SUBSTR(flow,1,INSTR(flow,'/')-1) AS flow, LISTAGG(prodorder,',') WITHIN GROUP(ORDER BY prodorder) AS prodorders, SUM(qty) AS qty
FROM (
    SELECT CASE WHEN flow=1 THEN '1/' ELSE to_CHAR(flow)||'/'||ROWNUM END AS flow,
        prodorder, qty FROM DATA
)
GROUP BY flow;