Question 191216 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Transform rows to column and filter out the values in postgres

I have two tables and the data in table 1 is in rows. Key column in table 1 has the columns that are as rows now and want to trasnform to column Below is how i have the table now.

The data i have in table 1 is as below

Table 1

tickedt_ID  Key     Value     Created_at
1   123     Free      2023-06-01
1   type    task      2023-06-01
1   status  open      2023-06-01
1   channel email     2023-06-01
1   456     fruit     2023-06-01
2   123     paid      2023-06-01
2   type    incident  2023-06-01
2   status  closed    2023-06-01
2   channel voice     2023-06-01
2   456     vegetable 2023-06-01

Table 2 has the created at date which i need to join with table 1 and also need to join subject and type.

Table 2

ID  Created_at  type        subject
1   2023-06-01  task        XXX
2   2023-06-01  incident    abc
3   2023-06-01  task        def

Code i tried quering

select t.ticket_id,t.created_at,t.value
    min(case when t.key = 123 then t.value end) as plan,
    min(case when t.key = 456 then t.value end) as category
from t1 t
join t2 te on t.ticket_id = te.id
where t.created_at > '2023-06-01' and t.created_at <= '2023-06-21' 
GROUP by t.id,t.created_at

when i try this the id is getting repeated again because they key has got multiple values and i am not able to group by the id

Expected output

ID.     123(rename as plan)     status.       456(rename as category).   type.      created at
1         Free                   open             Fruit                  task        2023-06-01
2         Paid                   closed           vege                   incident    2023-06-01
Tags:

2

Answers


  1. 0

    Your data wouldn’t return anything with that where criteria (no date is after 2023-06-01). Assuming that was a typo:

    CREATE TABLE t1 (
  ticket_ID INTEGER,
  Key VARCHAR(13),
  Value VARCHAR(20),
  Created_at TIMESTAMP
);

INSERT INTO t1
  (ticket_ID,Key,Value,Created_at)
VALUES
  ('1', '123', 'Free', '2023-06-01'),
  ('1', 'type', 'task', '2023-06-01'),
  ('1', 'status', 'open', '2023-06-01'),
  ('1', 'channel', 'email', '2023-06-01'),
  ('1', '456', 'fruit', '2023-06-01'),
  ('2', '123', 'paid', '2023-06-01'),
  ('2', 'type', 'incident', '2023-06-01'),
  ('2', 'status', 'closed', '2023-06-01'),
  ('2', 'channel', 'voice', '2023-06-01'),
  ('2', '456', 'vegetable', '2023-06-01');

CREATE TABLE t2 (
  ID INTEGER,
  Created_at TIMESTAMP,
  type VARCHAR(8),
  subject VARCHAR(3)
);

INSERT INTO t2
  (ID,Created_at,type,subject)
VALUES
  ('1', '2023-06-01', 'task', 'XXX'),
  ('2', '2023-06-01', 'incident', 'abc'),
  ('3', '2023-06-01', 'task', 'def');



select ticket_id,
    min(case when t1.key = '123' then t1.value end) as plan,
    min(case when t1.key = 'status' then t1.value end) as status,
    min(case when t1.key = '456' then t1.value end) as category,
    min(case when t1.key = 'type' then t1.value end) as "type",
    created_at
from t1
where t1.created_at >= '2023-06-01' and t1.created_at <= '2023-06-21' 
GROUP by ticket_id, created_at;
    ticket_id plan status category type created_at
    1 Free open fruit task 2023-06-01 00:00:00
    2 paid closed vegetable incident 2023-06-01 00:00:00

    DBFiddle demo

    Login or Signup to reply.
  2. 0

    Assuming that the table t1 has just one occurrence of distinct keys for each id, I believe you could use subselects to get the columns you want. Try like something this, and extend it with JOIN/WHERE/GROUP BY clauses as needed.

    SELECT t.id, 
       (SELECT value FROM t1 AS tplan WHERE tplan.id = t.id AND tplan.key = 123) as plan,
       (SELECT value FROM t1 AS tstat WHERE tstat.id = t.id AND tstat.key = 'status') as status,
       (SELECT value FROM t1 AS tcat WHERE tcat.id = t.id AND tcat.key = 456) as category,
FROM t1 AS t
GROUP BY t.id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search