行转列crosstab使用心得

2023-01-08 10:40:00
pjd
原创 16

一 首先,我形成并澄清了你的例子:

SELECT * FROM public.crosstab(
   $$VALUES ('na', 1, 100)   -- VALUES expression does the same, shorter
          , ('ba', 2, 300)$$ -- no column names needed (ignored anyway)
  ,$$VALUES ('1'), ('2')$$
   ) AS pivot (na text, co11 int, col2 int)  -- sane column names

输入表:
col1  key  val
---------------
na    1    100
ba    2    300

输出表
na    co11    col2
--------------------
na    100     <NULL>
ba    <NULL>  300

您可以自由选择输出列名,无需使用像"1" "2" 这样笨拙的名称。只有数据类型必须匹配。
动态返回类型
不能从查询中提供列定义列表。我们有很多类似的要求。它们都与SQL的性质冲突,SQL不接受“after-the-fact”返回类型。必须与查询一起声明,至少在执行查询时。
您必须使用要在第二个示例中添加的crosstab() 查询的输出来构建SELECT 查询。两次往返服务器。
但我们可以从系统目录中读取一个众所周知的类型,毕竟SELECT * FROM tbl 就是这样做的。我努力尝试并实现了我在这一相关答案中的发现:
Dynamic alternative to pivot with CASE and GROUP BY
我的功能可能是你最好的选择。阅读链接的答案!
它是crosstab_n() 的包装器,接受查询字符串(就像crosstab() )和提供返回类型的附加多态参数。我们仍然不能即时传递返回类型。SQL唯一可接受的方法是从系统目录中读取它。因此,我们通过创建一个临时表来“动态”安装一个复合类型:
CREATE TEMP TABLE my_pivot (na text, col1 int, col2 int);
SELECT * FROM crosstab_n(
    $$VALUES ('na', 1, 100), ('ba', 2, 100)$$
    ,NULL::my_pivot
    );

结果如上。
如果希望临时表仅用于事务,请将crosstab() 添加到其中。两个语句必须在一个事务中执行。细节:

Select from a table variable



二、

sql  postgresql  pivot  temp-tables  crosstab


发表评论
评论通过审核后显示。