please help me , I want to create table C with using table A and B ,I am new in oracle .what is the idea? how can I write this code ...... enter image description here

CodePudding user response：

One option is to conditionally aggregate values (but it also means that table B won't help much) (also, date is most probably not a column name as it is reserved word, reserved for the date datatype so I renamed it to datum):

select a.datum,
  sum(case when a.code = 1 then rate else 0 end) usd,
  sum(case when a.code = 2 then rate else 0 end) eur,
  sum(case when a.code = 3 then rate else 0 end) obp
from a
group by a.datum
order by a.datum;

DATUM              USD        EUR        OBP
----------- ---------- ---------- ----------
28-oct-2018       1000       2000       4000
29-oct-2018       2000       3000       5000
30-oct-2018       3000       4000       6000

CodePudding user response：

You can use a PIVOT:

SELECT *
FROM   (
  SELECT a."DATE",
         b.name,
         a.rate
  FROM   A
         INNER JOIN B
         ON (a.code = b.id)
)
PIVOT (
  SUM(rate)
  FOR name IN (
    'USD' AS usd,
    'EUR' AS eur,
    'OBP' AS obp
  )
)

or conditional aggregation:

SELECT a."DATE",
       SUM(CASE b.name WHEN 'USD' THEN a.rate END) AS usd,
       SUM(CASE b.name WHEN 'EUR' THEN a.rate END) AS eur,
       SUM(CASE b.name WHEN 'OBP' THEN a.rate END) AS obp
FROM   A
       INNER JOIN B
       ON (a.code = b.id)
GROUP BY a."DATE"

Which, for the sample data:

CREATE TABLE a (code, rate, "DATE") AS
  SELECT 1, 1000, DATE '2018-10-28' FROM DUAL UNION ALL
  SELECT 1, 2000, DATE '2018-10-29' FROM DUAL UNION ALL
  SELECT 1, 3000, DATE '2018-10-30' FROM DUAL UNION ALL
  SELECT 2, 2000, DATE '2018-10-28' FROM DUAL UNION ALL
  SELECT 2, 3000, DATE '2018-10-29' FROM DUAL UNION ALL
  SELECT 2, 4000, DATE '2018-10-30' FROM DUAL UNION ALL
  SELECT 3, 4000, DATE '2018-10-28' FROM DUAL UNION ALL
  SELECT 3, 5000, DATE '2018-10-29' FROM DUAL UNION ALL
  SELECT 3, 6000, DATE '2018-10-30' FROM DUAL

CREATE TABLE b (name, id) AS
  SELECT 'USD', 1 FROM DUAL UNION ALL
  SELECT 'EUR', 2 FROM DUAL UNION ALL
  SELECT 'OBP', 3 FROM DUAL;

Both output:

If you actually want to create a table then use CREATE TABLE ... AS SELECT ... with one of the above statements (or consider creating a view instead of a physical table).

fiddle