透视查询

片段

透视查询

适用于 PostgreSQL

任何

用

SQL

取决于

无

作者 Emanuel Calvo

参数= 您的查询。注意，查询中的第一列将是列名加上一个数字（对于您没有不同值的情况）。

CREATE OR REPLACE FUNCTION pivoty_with_header(query text) RETURNS void AS $pivot$
DECLARE
 num_cols int;
 num_rows int;
 table_pivoted text;
 columna text;
 header text;
 counter int := 0;
BEGIN
 DROP TABLE IF EXISTS pivoted;
 DROP TABLE IF EXISTS pivot_;
 
 EXECUTE 'CREATE TEMP TABLE pivot_ AS ' || query ;

 SELECT count(*) INTO num_cols from information_schema.columns where table_name = 'pivot_';
 SELECT count(*) INTO num_rows FROM pivot_;

 table_pivoted := 'CREATE TABLE pivoted (';

 SELECT column_name into header from information_schema.columns where table_name = 'pivot_' limit 1;
 FOR header IN EXECUTE 'SELECT ' || header || ' from pivot_'
 LOOP
    counter := counter +1;
   SELECT table_pivoted || 'col_' || translate(header,'.,|/?!','') || counter ||' text ,' INTO table_pivoted;
 END LOOP;
 select regexp_replace(table_pivoted,E'\,$','','g') INTO table_pivoted;
 select table_pivoted || ')' into table_pivoted;

 EXECUTE table_pivoted;
 
 
 FOR columna IN SELECT column_name::Text FROM  information_schema.columns where table_name = 'pivot_'
 LOOP
   if (columna != ('col_' || translate(header,'.,|/?!','')) )
   THEN
     EXECUTE 'INSERT INTO pivoted SELECT  ((translate(array_agg(' ||  columna || ')::text,''{}'',''()'' ))::pivoted).*  FROM pivot_';
   END IF;
 END LOOP;
 
END;
$pivot$ LANGUAGE plpgsql;

另一种不使用列名的方法

CREATE OR REPLACE FUNCTION pivoty(query text) RETURNS void AS $pivot$
DECLARE
 num_cols int;
 num_rows int;
 table_pivoted text;
 columna text;
BEGIN
 DROP TABLE IF EXISTS pivoted;
 DROP TABLE IF EXISTS pivot_;
 
 EXECUTE 'CREATE TEMP TABLE pivot_ AS ' || query ;

 SELECT count(*) INTO num_cols from information_schema.columns where table_name = 'pivot_';
 SELECT count(*) INTO num_rows FROM pivot_;

 table_pivoted := 'CREATE TABLE pivoted (';
 FOR i IN 1 .. num_rows 
 LOOP

   IF ( i = num_rows )  
   THEN
     SELECT table_pivoted || 'col' || i || ' text ' INTO table_pivoted;
   ELSE
     SELECT table_pivoted || 'col' || i || ' text ,' INTO table_pivoted;
   END IF;
 END LOOP;
 select table_pivoted || ')' into table_pivoted;

 EXECUTE table_pivoted;

 FOR columna IN SELECT column_name::Text FROM  information_schema.columns where table_name = 'pivot_'
 LOOP
   EXECUTE 'INSERT INTO pivoted SELECT  ((translate(array_agg(' ||  columna || ')::text,''{}'',''()'' ))::pivoted).*  FROM pivot_';
 
 END LOOP;

END;
$pivot$ LANGUAGE plpgsql;

此函数将生成一个名为 pivoted 的表，用于您的查询，因此使用查询作为参数执行它，然后

SELECT * FROM pivoted;