Flat to fat: sql column to multiple columns based on condition-CodePudding

Is there any way to create a pivot table with conversion of single column into multiple based on some if conditions (from flat to fat table)?

CREATE TABLE table_name (A,B,C,D) AS
  SELECT 'A', '1', '4', DATE '2000-01-04' FROM DUAL UNION ALL
  SELECT 'A', '1', '6', DATE '2000-01-04' FROM DUAL UNION ALL
  SELECT 'A', '2', '1', DATE '2000-01-04' FROM DUAL UNION ALL
  SELECT 'B', '1', '20', DATE '2000-01-04' FROM DUAL UNION ALL
  SELECT 'B', '2', '2', DATE '2000-01-04' FROM DUAL UNION ALL
  SELECT 'B', '-3', '999', DATE '2000-01-04' FROM DUAL UNION ALL
  SELECT 'A', '1', '30', DATE '2000-01-05' FROM DUAL UNION ALL
  SELECT 'B', '2', '3', DATE '2001-01-05' FROM DUAL;

The expect result per column A and D

A, D, C where B == 1, C where B == 2
A, 2000-01-04, 10, 1
B, 2000-01-04, 20, 2
A, 2000-01-05, 30, 0
B, 2000-01-05, 0, 3

or per column D

D, C where B == 1 - C where B == 2
2000-01-04, 27
2000-01-05, 27

CodePudding user response：

You can do:

select
  a,
  d,
  max(case when b = 1 then c end) as b1,
  max(case when b = 2 then c end) as b2
from table_name
group by a, d

and:

select
  d,
  max(case when b = 1 then c end) -
  max(case when b = 2 then c end) as x
from table_name
group by d

See running example at db<>fiddle.