Postgres PIVOT table on aggregated count

Question

I am trying to create a pivot table in Postgres from the following table named product_info:

CREATE TABLE product_info (
    ID varchar(10),
    date VARCHAR(20) NOT NULL,
    product VARCHAR(20) NOT NULL,
    product_cost integer 
);

INSERT INTO product_info(ID, date, product, product_cost)
VALUES
  ('1', 'AUG-23','Laptop',100),
  ('1', 'AUG-23','Phone',80),
  ('1', 'AUG-23','Keypad',50),
  ('1', 'SEP-23','Laptop',200),
  ('1', 'SEP-23','Phone',100),
  ('1', 'SEP-23','Ipad',150),
  ('1', 'SEP-23','Keypad',80),
  ('2', 'AUG-23','Laptop',100),
  ('2', 'AUG-23','Phone',90),
  ('2', 'AUG-23','Keypad',70),
  ('2', 'SEP-23','Laptop',120),
  ('2', 'SEP-23','Phone',30),
  ('2', 'SEP-23','Ipad',80),
  ('2', 'SEP-23','Keypad',40);

Tried below PIVOT Query

SELECT * FROM crosstab(
    'SELECT date, id,product, sum(product_cost)
     FROM product_info
     GROUP BY 1, 2,3
     ORDER BY 1, 2,3'
,
    'SELECT DISTINCT product FROM product_info ORDER BY 1'
) as cte(
    date text,
    id text, 
    "Ipad" numeric,
    "Keypad" numeric,
    "Laptop" numeric,
    "Phone" numeric
)

RETURNED RESULT

date	id	Ipad	Keypad	Laptop	Phone
AUG-23	1	NULL	70	100	90
SEP-23	1	80	40	120	30

I get a single row per month, with id = '1' and values for id = '2'.
I expected one row per ID.

EXPECTED RESULT

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

Answer 1

As you can use only obe row with crosstab, you can do something like this.

But simpler is a normal aggregation like the second query

SELECT split_part(id, '%', 1) as id,
  split_part(id, '%', 2) as date,
        "Ipad" ,
    "Laptop" ,
    "Phone" ,
    "Keypad" 
  FROM crosstab(
    'SELECT  CONCAT(id,''%'',date) as id, product, sum(cost)
     FROM product_info
     GROUP BY 1, 2
     ORDER BY 1, 2'
,
    'SELECT DISTINCT product FROM product_info ORDER BY 1'
) as cte(
    id text,
    "Ipad" numeric,
    "Laptop" numeric,
    "Phone" numeric,
    "Keypad" numeric
)

id	date	Ipad	Laptop	Phone	Keypad
1	Aug-23	null	50	250	100
1	Sep-23	200	60	260	100
2	Aug-23	null	80	220	100
2	Sep-23	90	80	100	100

SELECT
  id,date,
  SUM(CASE WHEN product = 'Ipad' THEN cost END ) as "Ipad",
  SUM(CASE WHEN product = 'Laptop' THEN cost END ) as "Laptop",
  SUM(CASE WHEN product = 'Phone' THEN cost END ) as "Phone",
  SUM(CASE WHEN product = 'Keypad' THEN cost END ) as "Keypad"
FROM 
product_info
GROUP BY id,date
ORDER BY id,date

id	date	Ipad	Laptop	Phone	Keypad
1	Aug-23	null	250	100	50
1	Sep-23	200	260	100	60
2	Aug-23	null	220	100	80
2	Sep-23	90	100	100	80

fiddle