postgresql 将行转换为列并过滤掉postgres中的值

bcs8qyzn 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(2)|浏览(127)

我有两个表，表1中的数据是以行为单位的。表1中的关键列具有现在作为行的列，并且希望转换为列下面是我现在如何拥有该表的。
我在表1中的数据如下
表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

表2有创建日期，我需要与表1连接，还需要连接主题和类型。
表2

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

代码我尝试查询

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

当我尝试这个id是越来越重复再次因为他们的关键字有多个值，我不能分组的id
预期产量

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

postgresql

来源：https://stackoverflow.com/questions/76526688/transform-rows-to-column-and-filter-out-the-values-in-postgres

2条答案

按热度按时间

yvfmudvl1#

假设表t1中的每个id只出现一次不同的键，我相信您可以使用subselects来获得所需的列。尝试像这样，并根据需要使用JOIN/WHERE/GROUP BY子句扩展它。

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

赞(0）回复(0）举报 2023-06-22

5ssjco0h2#

您的数据不会返回任何符合条件的内容（没有日期是2023-06-01之后）。假设这是一个错字：

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;

| 票证ID|计划|地位|类别|类型，类型|创建于|
| - -----|- -----|- -----|- -----|- -----|- -----|
| 1|免费|打开|水果|任务|2023-06-01 00：00：00|
| 2|已付|封闭的|蔬菜|偶发事件|2023-06-01 00：00：00|
DBFiddle demo

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql 将行转换为列并过滤掉postgres中的值

2条答案

相关问题

热门标签

最新问答