1

Here is my table view. [column names & records].

MERCHANT_ID |CARD_TYPE  |DENOMINATION   |STOCK_IN_HAND

1000017     |CDMA_PP    |1000           | 5 
10000000    |CDMA_PP    |50             | 1 
10000000    |CDMA_PP    |500            | 1 
10000000    |CDMA_PP    |1000           | 1 
10000001    |CDMA_PP    |1000           | 1

Now my requirement is need to create table with seperate columns for those card_denominations.

MERCHANT_ID |CARD_TYPE  |500       |1000     |STOCK_IN_HAND

1000017     |CDMA_PP    | 0        | 1       | 1
10000000    |CDMA_PP    | 1        | 1       | 2
10000001    |CDMA_PP    | 0        | 1       | 1

so please help me to sort out this issue. [stock in hand shows total cards ] thanks.

Priyan RockZ
  • 1,605
  • 7
  • 40
  • 68
  • Since you tagged this with PIVOT, you know the proper term...have you tried writing the query? It would be really helpful to see your attempts at getting the final result. – Taryn Feb 20 '14 at 12:36
  • i tried with mentioned links.but those are not yet supported .version 11 upwards must.? – Priyan RockZ Feb 21 '14 at 03:51

1 Answers1

1

This should be a starting point:

SELECT 
*
FROM
(
SELECT 1000017  as merchant_id   ,'CDMA_PP' as cardtype, 1000 as denomination, 5 as stock from dual 
UNION ALL SELECT 10000000,'CDMA_PP',50,1 from dual 
UNION ALL SELECT 10000000,'CDMA_PP',500,1 from dual 
UNION ALL SELECT 10000000,'CDMA_PP',1000,1  from dual
UNION ALL SELECT 10000001,'CDMA_PP',1000,1  from dual
)
PIVOT
(SUM(stock)
FOR denomination in (50,500,1000))
Dibstar
  • 2,334
  • 2
  • 24
  • 38
  • thanks dear Dibstar.but when i added final part PIVOT (SUM(stock)FOR denomination in (50,500,1000) part then its shows error ORA-00933: SQL command not properly ended – Priyan RockZ Feb 21 '14 at 03:26