PIVOT multiple rows into columns

Question

I have table like below. Consider the query

select invoice_mth, inv_amt from table xdetails
 where mobile_number=9080808080

data in the table

mobile_number   invoice_mth  inv_amt
 9080808080     2010-10        20
 9080808080     2010-11        30
 9080808080     2010-12        40

I have to display the data from table like below.

I want invoice months to separate each month and amt separately.

MOBILE_NUMBER   inv_m1   inv_m2    inv_m3   amt1    amt2    amt3
------- ----------------------------------------------------------
 9080808080    2010-10   2010-11   2010-12   20       30      40

to display the data like above what I have to do?

There are plenty of plenty of pivoting questions and answers on Stackoverflow. If your situation is urgent you should read some of the threads in this search result set. The main question is, do you know the number of rows you want to pivot upfront? Or do you need a dynamic solution? — APC
– APC, Commented Mar 17, 2015 at 8:06

Lalit Kumar B · Accepted Answer · 2015-03-17 08:18:43Z

3

You could play around with the standard PIVOT query:

SQL> SELECT * FROM t;

MOBILE_NUMBER INVOICE    INV_AMT
------------- ------- ----------
   9080808080 2010-10         20
   9080808080 2010-11         30
   9080808080 2010-12         40

SQL>
SQL> SELECT *
  2  FROM
  3    (SELECT mobile_number, invoice_mth, inv_amt FROM t
  4    ) PIVOT (MIN(invoice_mth) AS inv_mth,
  5             SUM(inv_amt) AS inv_amt
  6             FOR (invoice_mth) IN ('2010-10' AS m1, '2010-11' AS m2, '2010-12' AS m3))
  7  ORDER BY mobile_number;

MOBILE_NUMBER M1_INV_ M1_INV_AMT M2_INV_ M2_INV_AMT M3_INV_ M3_INV_AMT
------------- ------- ---------- ------- ---------- ------- ----------
   9080808080 2010-10         20 2010-11         30 2010-12         40

SQL>

answered Mar 17, 2015 at 8:18

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

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

5 Comments

CGNath Over a year ago

105 57060 36069 WCT 5.25% 5.25 105 57060 36069 Sec 194C - Contractor - Non Individual 2% PWCPL 2 105 57060 36070 Sec 194C - Contractor - Non Individual 2% PWCPL 2 105 57060 36070 WCT 5.25% 5.25 105 57060 36071 WCT 5.25% 5.25 105 57060 36072 Sec 194C - Contractor - Non Individual 2% PWCPL 2 105 57060 36072 WCT 5.25% 5.25 105 57060 36073 WCT 5.25% 5.25 105 57060 36074 WCT 5.25% 5.25 105 57060 36074 Sec 194C - Contractor - Non Individual 2% PWCPL 2

Lalit Kumar B Over a year ago

Please mark it as answered, it will help others as well.

MontyPython Over a year ago

The pivot function rocks! :)

CGNath Over a year ago

Hi Lalith, here IN class u r passing static values, but if we want to make this values as non static(dynamic) values How can we do this. I have tried PIVOT XML but it is displaying the XML tags, not displaying in the table form. Can u provide tha solution for this..Pls I am facing problem with this issue last one week..I am very thankful for U r reply...

Lalit Kumar B Over a year ago

I understand your concern. however, for dynamic pivoting the only way I know is using PL/SQL, Tom Kyte has demonstrated an example here oracle.com/technetwork/issue-archive/2012/12-jul/…

MT0 · Accepted Answer · 2015-11-06 01:09:42Z

If you want a fixed number of columns in the output:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TABLE_NAME ( mobile_number, invoice_mth, inv_amt ) AS
          SELECT 9080808080, '2010-10', 20 FROM DUAL
UNION ALL SELECT 9080808080, '2010-11', 30 FROM DUAL
UNION ALL SELECT 9080808080, '2010-12', 40 FROM DUAL;

Query 1:

SELECT mobile_number,
       MAX( CASE RN WHEN 1 THEN invoice_mth END ) AS inv_m1,
       MAX( CASE RN WHEN 2 THEN invoice_mth END ) AS inv_m2,
       MAX( CASE RN WHEN 3 THEN invoice_mth END ) AS inv_m3,
       MAX( CASE RN WHEN 1 THEN inv_amt END ) AS amt1,
       MAX( CASE RN WHEN 2 THEN inv_amt END ) AS amt2,
       MAX( CASE RN WHEN 3 THEN inv_amt END ) AS amt3
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY mobile_number ORDER BY invoice_mth ASC ) AS rn
  FROM   TABLE_NAME t
)
GROUP BY mobile_number

Results:

| MOBILE_NUMBER |  INV_M1 |  INV_M2 |  INV_M3 | AMT1 | AMT2 | AMT3 |
|---------------|---------|---------|---------|------|------|------|
|    9080808080 | 2010-10 | 2010-11 | 2010-12 |   20 |   30 |   40 |

Collectives™ on Stack Overflow

PIVOT multiple rows into columns

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related