如何编写一个将这种形式的数据转换为所需结果的数据透视图

Question

我有一个看起来像的表

CREATE TABLE foo (id, name, category)
AS VALUES
( 1, 'name1.1', 'cat1.1.1'),
( 1, 'name1.2', 'cat1.1.1'),
( 1, 'name1.3', 'cat1.2.1'),
( 2, 'name2.1', 'cat2.1.1'),
( 2, 'name2.2', 'cat2.1.1'),
( 3, 'name3.1', 'cat3.1.1')
;

，我正试图得到这样的结果

>名称3

Id	名称1	名称2
1	名称1.1	名称1.2	姓名1.3
2	名称2.1	名称2.2
3		名称3.1

我会将所有名称聚合到一个数组中，然后将数组元素提取为列：

select id, 
names[1] as name1,
names[2] as name2,
names[3] as name3
from (
select id, 
array_agg(name order by name) as names
from foo
group by id
) t   

如果名称可以包含类似name10.11的内容，那么列的顺序将不是数字的，因为字符串'10'低于字符串'2'。如果你想让订单反映数字，排序会变得有点复杂：

array_agg(name order by string_to_array(replace(name, 'name', ''), '.')::int[]) as names

这将删除name前缀，并将数字转换为整数数组，然后进行正确排序。另一种选择是删除所有不是数字或点的东西：

array_agg(name order by string_to_array(regexp_replace(name, '[^0-9.]', '', 'g'), '.')::int[]) as names

首先需要添加提供此功能的tablefunc模块。

CREATE EXTENSION tablefunc;

现在你需要这样的查询，

SELECT *
FROM crosstab(
-- here we normalize this into `id | cat | value`
-- where the category is [1-3]
$$SELECT id, RIGHT(name,1)::int AS cat, name AS value FROM foo ORDER BY 1,2;$$,
-- For just the cat 1,2,3
$$VALUES (1),(2),(3);$$
) AS ct(id text, name1 text, name2 text, name3 text);

，哪个会返回这个

id |  name1  |  name2  |  name3  
----+---------+---------+---------
1  | name1.1 | name1.2 | name1.3
2  | name2.1 | name2.2 | 
3  | name3.1 |         | 
(3 rows)

注意这里的大问题，你的分类实际上是你的数据RIGHT(name,1)::int的函数。那可能是你的阻碍。相反，您在category中提供的实际数据似乎可以完全忽略，除非我遗漏了什么。

还要注意，我在两个地方硬编码了分类名称，

$$VALUES (1),(2),(3);$$

而且，

ct(id text, name1 text, name2 text, name3 text);

这是必需的，因为PostgreSQL不允许您在运行命令时返回结果集未知的查询。这将只支持[name1 - name3]。如果你想让它真正动态，程序的范围会增长很多，这就偏离了这个问题的主题。