我尝试在PostgreSQL中创建crosstab
查询,以便它自动生成crosstab
列,而不是硬编码它。我写了一个函数,动态生成crosstab
查询所需的列列表。其想法是使用动态SQL在crosstab
查询中替换此函数的结果。
如何在PostgreSQL中做到这一点?
-- Table which has be pivoted
CREATE TABLE test_db
(
kernel_id int,
key int,
value int
);
INSERT INTO test_db VALUES
(1,1,99),
(1,2,78),
(2,1,66),
(3,1,44),
(3,2,55),
(3,3,89);
-- This function dynamically returns the list of columns for crosstab
CREATE FUNCTION test() RETURNS TEXT AS '
DECLARE
key_id int;
text_op TEXT = '' kernel_id int, '';
BEGIN
FOR key_id IN SELECT DISTINCT key FROM test_db ORDER BY key LOOP
text_op := text_op || key_id || '' int , '' ;
END LOOP;
text_op := text_op || '' DUMMY text'';
RETURN text_op;
END;
' LANGUAGE 'plpgsql';
-- This query works. I just need to convert the static list
-- of crosstab columns to be generated dynamically.
SELECT * FROM
crosstab
(
'SELECT kernel_id, key, value FROM test_db ORDER BY 1,2',
'SELECT DISTINCT key FROM test_db ORDER BY 1'
)
AS x (kernel_id int, key1 int, key2 int, key3 int); -- How can I replace ..
-- .. this static list with a dynamically generated list of columns ?
字符串
4条答案
按热度按时间llew8vvj1#
你可以使用提供的C函数
crosstab_hash
。说明书在这方面不是很清楚,提到了at the end of the chapter on
crosstab()
with two parameters:您可以创建预定义的函数,以避免在每个查询中写出结果列名和类型。请参阅上一节中的示例。这种形式的
crosstab
的底层C函数名为crosstab_hash
。举个例子:
字符串
电话:
型
请注意,您需要为每个具有不同返回类型的
crosstab
函数创建一个不同的crosstab_hash
函数。相关:
您的 * 函数生成列列表 * 相当复杂,结果不正确(
kernel_id
之后缺少int
),可以用以下SQL查询替换:型
而且无论如何都不能动态使用。
iibxawm42#
我知道这是一个老职位,但在同一问题上挣扎了一段时间。
**我的问题陈述:**我有一个字段中有多个值的表,并希望创建一个每行有40多个列标题的交叉表查询。
我的解决方案是创建一个函数,该函数循环遍历表列,以获取我希望用作交叉表查询中列标题的值。
在这个函数中,我可以创建交叉表查询。在我的用例中,我将这个交叉表结果添加到一个单独的表中。
例如
字符串
wj8zmpe13#
@erwin-brandstetter:如果你总是返回一个JSON类型的转换结果,那么函数的返回类型就不是问题。
下面是我提出的函数:
字符串
所以,当你运行它时,你会得到JSON格式的动态结果,你不需要知道有多少值被旋转:
型
编辑:如果你的交叉表中有混合数据表,你可以添加逻辑来查找每一列,如下所示:
型
xzv2uavs4#
here描述的方法对我来说很有效。而不是直接检索pivot表。更简单的方法是让函数生成SQL查询字符串。根据需要动态执行结果SQL查询字符串。
动态查询模式简介:
字符串