CODEWITHSUNDEEP
sqloracle-database
snso
snso

Reputation: 329

How to turn query result into another column that depends on different reference number but same year?

I have an Oracle table dummy which contains 3 columns reference_no, year and amount.

My question is how do compare each row and move the one that different to another column?

I have data like this

+--------------+-------+--------+ 
| REFERENCE_NO | YEAR  | AMOUNT |
+--------------+-------+--------+
| A01          | 2010  |   -100 |
| A01          | 2011  |   -100 |
| A01          | 2012  |   -100 |
| B02          | 2012  |  -2000 |
| A01          | 2013  |   -100 |
| B02          | 2013  |  -2000 |
+--------------+-------+--------+

But the result I want is like this

+------+------------+------------+
| YEAR | AMOUNT A01 | AMOUNT B02 |
+------+------------+------------+
| 2010 |       -100 |            |
| 2011 |       -100 |            |
| 2012 |       -100 |      -2000 |
| 2013 |       -100 |      -2000 |
+------+------------+------------+

What is the best way to do this? It's like grouping by year and move the not same row to a new column.

http://sqlfiddle.com/#!4/b1c7f7/3

Sorry. My english is bad. Thanks

Upvotes: 0

Views: 44

Answers (2)

JohnHC
JohnHC

Reputation: 11195

Self join

select a1.year, a1.amount as a01_amount, b2.amount as b02_amount
from dummy a1
left join dummy b2
  on a1.year = b2.year
  and b2.reference_no = 'B02'
where a1.reference_no = 'A01'
ORDER BY a1.year;

Upvotes: 1

WJS
WJS

Reputation: 714

you would need to PIVOT the data as described here: https://www.techonthenet.com/oracle/pivot.php

SELECT * FROM
(
  SELECT year, reference_no, amount 
  FROM dummy
)
PIVOT
(
  SUM(amount)
  FOR reference_no IN ('A01','B02' )
)
ORDER BY year;

Upvotes: 0

Related Questions