Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE product_info (
  id int
, date date NOT NULL
, product text NOT NULL
, product_cost int
);

INSERT INTO product_info VALUES
  (1, '2023-08-01', 'Laptop', 100)
, (1, '2023-08-01', 'Phone', 80)
, (1, '2023-08-01', 'Keypad', 50)
, (1, '2023-09-01', 'Laptop', 200)
, (1, '2023-09-01', 'Phone', 100)
, (1, '2023-09-01', 'Ipad', 150)
, (1, '2023-09-01', 'Keypad', 80)
, (2, '2023-08-01', 'Laptop', 100)
, (2, '2023-08-01', 'Phone', 90)
, (2, '2023-08-01', 'Keypad', 70)
, (2, '2023-09-01', 'Laptop', 120)
, (2, '2023-09-01', 'Phone', 30)
, (2, '2023-09-01', 'Ipad', 80)
, (2, '2023-09-01', 'Keypad', 40) 
;

CREATE TABLE



INSERT 0 14


-- base query
SELECT dense_rank() OVER (ORDER BY date, id) AS rn  -- "row name"
     , to_char(date, 'MON-YY') AS date, id          -- "extra columns"
     , product                                      -- "key"
     , sum(product_cost) AS sum_cost                -- "value"
FROM   product_info
GROUP  BY date, id, product
ORDER  BY date, id, product;


rn
date
id
product
sum_cost


1
AUG-23
1
Keypad
50

1
AUG-23
1
Laptop
100

1
AUG-23
1
Phone
80

2
AUG-23
2
Keypad
70

2
AUG-23
2
Laptop
100

2
AUG-23
2
Phone
90

3
SEP-23
1
Ipad
150

3
SEP-23
1
Keypad
80

3
SEP-23
1
Laptop
200

3
SEP-23
1
Phone
100

4
SEP-23
2
Ipad
80

4
SEP-23
2
Keypad
40

4
SEP-23
2
Laptop
120

4
SEP-23
2
Phone
30



SELECT 14


-- crosstab
-- CREATE TABLE pivot_test AS (
SELECT date, id, "Ipad", "Keypad", "Laptop", "Phone" 
FROM crosstab(
   $q$
   SELECT dense_rank() OVER (ORDER BY date, id)
        , to_char(date, 'MON-YY'), id
        , product
        , sum(product_cost)
   FROM   product_info
   GROUP  BY date, id, product
   ORDER  BY date, id, product
   $q$
,  $v$
   SELECT unnest('{Ipad, Keypad,Laptop, Phone}'::text[])
   $v$
   ) AS ct(rn int, date text, id int
         , "Ipad"   int
         , "Keypad" int
         , "Laptop" int
         , "Phone"  int);


date
id
Ipad
Keypad
Laptop
Phone


AUG-23
1
null
50
100
80

AUG-23
2
null
70
100
90

SEP-23
1
150
80
200
100

SEP-23
2
80
40
120
30



SELECT 4


-- plain SQL
SELECT to_char(date, 'MON-YY') AS date, id
     , sum(product_cost) FILTER (WHERE product = 'Ipad')   AS "Ipad"
     , sum(product_cost) FILTER (WHERE product = 'Keypad') AS "Keypad"
     , sum(product_cost) FILTER (WHERE product = 'Laptop') AS "Laptop"
     , sum(product_cost) FILTER (WHERE product = 'Phone')  AS "Phone"
FROM   product_info
GROUP  BY date, id
ORDER  BY date, id;


date
id
Ipad
Keypad
Laptop
Phone


AUG-23
1
null
50
100
80

AUG-23
2
null
70
100
90

SEP-23
1
150
80
200
100

SEP-23
2
80
40
120
30



SELECT 4

rn	date	id	product	sum_cost
1	AUG-23	1	Keypad	50
1	AUG-23	1	Laptop	100
1	AUG-23	1	Phone	80
2	AUG-23	2	Keypad	70
2	AUG-23	2	Laptop	100
2	AUG-23	2	Phone	90
3	SEP-23	1	Ipad	150
3	SEP-23	1	Keypad	80
3	SEP-23	1	Laptop	200
3	SEP-23	1	Phone	100
4	SEP-23	2	Ipad	80
4	SEP-23	2	Keypad	40
4	SEP-23	2	Laptop	120
4	SEP-23	2	Phone	30

date	id	Ipad	Keypad	Laptop	Phone
AUG-23	1	null	50	100	80
AUG-23	2	null	70	100	90
SEP-23	1	150	80	200	100
SEP-23	2	80	40	120	30