英文:
Transform rows to column and filter out the values in postgres
问题
我有两个表格，表格1中的数据是按行排列的。表格1中的关键列现在作为行，我想要将其转换为列。以下是我现在的表格内容。
我在表格1中的数据如下：
表格1
ticket_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(重命名为plan) status. 456(重命名为category). type. created at
1 Free open Fruit task 2023-06-01
2 Paid closed vege incident 2023-06-01
英文:
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
答案1
得分: 0
您的数据不会返回任何内容，因为没有日期在2023年06月01日之后。假设这是一个打字错误：
```sql
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 |
<details>
<summary>英文:</summary>
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](https://dbfiddle.uk/NEAVDAem)
</details>
# 答案2
**得分**: 0
假设表t1对于每个id只有一个不同键的出现，我认为您可以使用子查询来获取所需的列。尝试像这样，根据需要使用JOIN/WHERE/GROUP BY子句进行扩展。
```sql
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论