ORACLE：將數據分組更改為基於月份細分

為了保持簡短和重點，我正在考慮修改此 SQL 查詢的分組以從NAME到Month/yy

這是腳本的範例，以及輸出文件的螢幕截圖

這就是我想要完成的。

關於如何解決這個問題的任何想法？

SQL 腳本：

SELECT A.NAME
    COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.ORDER_NO ELSE NULL END)) AS OCT11_ORDERS
    , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.ORDER_NO ELSE NULL END) AS OCT11_LINES
    , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.INVOICE_NO ELSE NULL END)) AS OCT11_INV_COUNT
    , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.INVOICE_NO ELSE NULL END) AS OCT11_INV_LINES
-- NOV 11 
    , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.ORDER_NO ELSE NULL END)) AS NOV11_ORDERS
    , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.ORDER_NO ELSE NULL END) AS NOV11_LINES
    , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.INVOICE_NO ELSE NULL END)) AS NOV11_INV_COUNT
    , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.INVOICE_NO ELSE NULL END) AS NOV11_INV_LINES
-- DEC 11     
    , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.ORDER_NO ELSE NULL END)) AS DEC11_ORDERS
    , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.ORDER_NO ELSE NULL END) AS DEC11_LINES
    , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.INVOICE_NO ELSE NULL END)) AS DEC11_INV_COUNT
    , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.INVOICE_NO ELSE NULL END) AS DEC11_INV_LINES

FROM test.test1 A 
WHERE TO_DATE(A.INVDATE) BETWEEN TO_DATE('01-OCT-11') AND TO_DATE('30-SEP-12')
AND COMPANY = '30'
AND CUST_GRP IN ('3','4')
GROUP BY A.NAME 

輸出文件：

期望的輸出

對於所需的輸出，所有列都應該是counts

謝謝！

我想通了，不得不group by trucn(invdate,'mm')和count單獨的列

SELECT TO_CHAR(TRUNC(inv.invdate,'MM'),'MON-RR') "Month",
        COUNT(DISTINCT(inv.order_no)) "Orders",
        COUNT(inv.order_no) "Order Lines",
        COUNT(DISTINCT(inv.invoice_no)) "Invoices",
        COUNT(inv.invoice_no) "Invoice Lines",
        COUNT(DISTINCT(inv.name)) "Companies"
FROM test.test1 inv
GROUP BY TRUNC(inv.invdate,'MM')

引用自：https://dba.stackexchange.com/questions/139023