我有一个CTE，我在其中获得详细信息如下。我希望这个结果根据位置进行旋转。但是列名需要相同(如果不是可以不同(。
示例输出如下，任何人都可以建议或帮助吗？
PostgreSQL 版本 11
表有近 2000 个 ID 和最多 10 个链接。示例输入：
ID LINK Position
757 "https://www.shop.com/product/5/_/5_142_40.jpg" 4
757 "https://www.shop.com/product/3/_/3_347_44.jpg" 2
757 "https://www.shop.com/product/4/_/4_207_43.jpg" 3
757 "https://www.shop.com/product/2/_/2_416_44.jpg" 1
758 "https://www.shop.com/product/5/_/5_142_39.jpg" 4
758 "https://www.shop.com/product/4/_/4_207_42.jpg" 3
758 "https://www.shop.com/product/3/_/3_347_43.jpg" 2
758 "https://www.shop.com/product/2/_/2_416_43.jpg" 1
760 "https://www.shop.com/product/5/_/5_142_42.jpg" 4
示例输出：
ID link link link link
757 .../2/_/2_416_44.jpg ../3/_/3_347_44.jpg ../4/_/4_207_43.jpg ../5/_/5_142_40.jpg
758 .../2/_/2_416_43.jpg ../3/_/3_347_43.jpg ../4/_/4_207_42.jpg. ../5/_/5_142_39.jpg
760 ../5/_/5_142_42.jpg
您可以使用row_number()和条件聚合。不过，列名需要不同：
with mycte as (...) -- your cte here
select
id,
max(link) filter(where rn = 1) link1,
max(link) filter(where rn = 2) link2,
max(link) filter(where rn = 3) link3,
max(link) filter(where rn = 4) link4
from (
select c.*, row_number() over(partition by id order by position) rn
from mycte c
) t
group by id