How to display Oracle query output in Matrix/Pivot format

Question

I would like to display the output in Matrix/Pivot format of the following query.

Query :

SELECT
  SUBSTR(mon, 4, 6) month,
  rmbs_cd,
  scdta,
  cl_nm,
  br_cd,
  brd_nm,
  prod,
  prod_nm,
  SUM(sale_net)     sales
FROM (SELECT
        LAST_DAY(x.deli_dt)                                    mon,
        x.rmbs_cd,
        x.sc_cd || x.dist_cd || x.tha_cd || x.un_cd || x.cl_id scdta,
        INITCAP(cl_nm)                                         cl_nm,
        a.br_cd,
        brd_nm,
        a.cat_cd || a.prd_cd                                   prod,
        prod_nm,
        sale_cd,
        Nvl(sum(a.sale_net) - sum(rt_qty * flat_rt), 0)        sale_net
      FROM bill_det a, bill_mas x, cl_info c, inv_brand d, inv_prod p
      WHERE (a.bill_no = x.bill_no AND a.sc_cd = x.sc_cd)
            AND x.fl_mvh IN ('1', '4')
            AND x.deli_dt BETWEEN '01-JUL-15' AND '31-DEC-15'
            AND a.br_cd = d.br_cd AND d.div_cd = '1'
            AND a.typ_cd || a.cat_cd || a.prd_cd = p.typ_cd || p.cat_cd || p.prd_cd
            AND p.typ_cd = '09'
            AND x.sc_cd = c.sc_cd (+)
            AND x.dist_cd = c.dist_cd (+)
            AND x.tha_cd = c.tha_cd (+)
            AND x.un_cd = c.un_cd (+)
            AND x.cl_id = c.cl_id (+)
            AND c.div_cd IN ('1', '4')
            AND sale_cd IN ('IM', 'IC', 'IN')
            AND cancl IS NULL
      GROUP BY
        LAST_DAY(x.deli_dt),
        x.rmbs_cd,
        x.sc_cd || x.dist_cd || x.tha_cd || x.un_cd || x.cl_id,
        cl_nm,
        a.br_cd,
        brd_nm,
        a.cat_cd || a.prd_cd,
        prod_nm,
        sale_cd
)
GROUP BY SUBSTR(mon, 4, 6), rmbs_cd, scdta, cl_nm, br_cd, brd_nm, prod, prod_nm
ORDER BY 1, 2, 3

Result :

Expected Output :

I would like to display each individual month in different vertical columns sequentially in Oracle.

You find some suggestions here. It's imporant to understand if you have a maximum number of months, or they can be unlimited: when pivoting, you have to know in advance the number of columns you'll have after pivoting ( or build some dynamic SQL) — Aleksej
– Aleksej, Commented Feb 9, 2016 at 11:05
some time ago I met with 'dynamic pivoting', look here: community.oracle.com/thread/883038 — rtbf
– rtbf, Commented Feb 9, 2016 at 11:58

user1 · Accepted Answer · 2016-02-09 14:45:43Z

1

Hi
something like this (tested on Oracle 11gr2)? Using Pivot function:

WITH T AS(SELECT 'A' AS PROD, 1 AS VAL, 1 AS myMONTH FROM DUAL
UNION ALL
SELECT 'A' AS PROD, 20 AS VAL, 2 AS myMONTH FROM DUAL
UNION ALL
SELECT 'A' AS PROD, 33 AS VAL, 3 AS myMONTH FROM DUAL
UNION ALL
SELECT 'A' AS PROD, 13 AS VAL, 4 AS myMONTH FROM DUAL
UNION ALL
SELECT 'B' AS PROD, 3211 AS VAL, 5 AS myMONTH FROM DUAL
UNION ALL
SELECT 'C' AS PROD, 1 AS VAL, 6 AS myMONTH FROM DUAL
UNION ALL
SELECT 'D' AS PROD, 1 AS VAL, 7 AS myMONTH FROM DUAL
UNION ALL
SELECT 'D' AS PROD, 32 AS VAL, 7 AS myMONTH FROM DUAL
UNION ALL
SELECT 'E' AS PROD, 1 AS VAL, 8 AS myMONTH FROM DUAL
UNION ALL
SELECT 'B' AS PROD, 1 AS VAL, 9 AS myMONTH FROM DUAL
UNION ALL
SELECT 'G' AS PROD, 2131 AS VAL, 9 AS myMONTH FROM DUAL
UNION ALL
SELECT 'A' AS PROD, 1 AS VAL, 10 AS myMONTH FROM DUAL
UNION ALL
SELECT 'H' AS PROD, 1 AS VAL, 11 AS myMONTH FROM DUAL
UNION ALL
SELECT 'J' AS PROD, 234 AS VAL, 1 AS myMONTH FROM DUAL
UNION ALL
SELECT 'J' AS PROD, 432 AS VAL, 3 AS myMONTH FROM DUAL
UNION ALL
SELECT 'J' AS PROD, 22 AS VAL, 5 AS myMONTH FROM DUAL
UNION ALL
SELECT 'J' AS PROD, 25546 AS VAL, 5 AS myMONTH FROM DUAL)
SELECT *
FROM (
SELECT PROD, VAL, MYMONTH
FROM T)
PIVOT(SUM(VAL) FOR MYMONTH IN (1 AS JAN, 2 AS FEB, 3 AS MAR, 4 AS APR, 5 AS MAY, 6 AS JUN,
7 AS JUL, 8 AS AGO, 9 AS SEPT, 10 AS OCT, 11 AS NOV, 12 AS DIC)
)
ORDER BY
 PROD;

Igor

edited Feb 9, 2016 at 14:45

answered Feb 9, 2016 at 14:39

user1

5862 gold badges8 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Iftekhar Over a year ago

thanks Igor for the answer.i am using Oracle 10g at work and it doesn't support the Pivot function

Collectives™ on Stack Overflow

How to display Oracle query output in Matrix/Pivot format

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related