CODEWITHSUNDEEP
sqloracle-databasepivot
Iftekhar
Iftekhar

Reputation: 365

How to display Oracle query output in Matrix/Pivot format

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 :

mat

Expected Output :

expected

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

Upvotes: 0

Views: 5198

Answers (1)

user1
user1

Reputation: 586

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

Upvotes: 1

Related Questions