将一列转换为多行,列数减少
问题描述:
我想在PostgreSQL中将单行转换成多行,其中某些列已删除。以下是当前输出的示例:
I'd like to convert single rows into multiple rows in PostgreSQL, where some of the columns are removed. Here's an example of the current output:
name | st | ot | dt |
-----|----|----|----|
Fred | 8 | 2 | 3 |
Jane | 8 | 1 | 0 |
Samm | 8 | 0 | 6 |
Alex | 8 | 0 | 0 |
使用以下查询:
SELECT
name, st, ot, dt
FROM
times;
这就是我想要的:
name | t | val |
-----|----|-----|
Fred | st | 8 |
Fred | ot | 2 |
Fred | dt | 3 |
Jane | st | 8 |
Jane | ot | 1 |
Samm | st | 8 |
Samm | dt | 6 |
Alex | st | 8 |
如何修改查询以获得上述所需的输出?
How can I modify the query to get the above desired output?
答
SELECT
times.name, x.t, x.val
FROM
times cross join lateral (values('st',st),('ot',ot),('dt',dt)) as x(t,val)
WHERE
x.val <> 0;