pgsql crosstab function - Mailing list pgsql-hackers

Hello all .. i just wrote this script in plpgsql. Basically it performs a crosstab query. The difference between this
andthe one you already have in contrib is that this one has no limitations on the arguments passed to it. The headers
ofthe resulting query are passed as a csv string so it can grow without having to create a function for every crosstab
querywe want to execute. The functions included are 2. One returns a sql string which can then be executed and the
otherperforms the sql from the function itself and produces output. I tested it on a couple of tables and it seems to
workquite well.. All the instructions with a very very simple test are included in the .sql file. Please test it out
anddecide if it can be used with the postgresql distribution. Hope this can be a start for a new functionallity. By the
way.. if anyone wants to rewrite it in C lease do it .. probably it could have a huge boost in performance .. Btw .. i
testedthis only in pg 7.4beta5 ..
 


Best Regards,

Fabrizio Mazzoni..

/*
Function by Fabrizio Mazzoni, veramente@libero.it 1/11/2003

You can use these functions as you like. If you have any comments or suggestions please email me
at the cited email address.

Transform as in ms-access (Crosstab query). This script produces 2 functions

transformstr(text,text,text,text,text,text) --> This is a function that will produce a SQL string that will perform a
crosstabquery for you.
 
transform(text,text,text,text,text,text) --> This is a set returning function that will produce a result in psql

Notes:
It is *VERY* important that in the column reference , the last piece of the csv must be a ";" (semicolon) eg.
select transform('col1', 'select * from foo', 's;m;l;xl;xxl;,'null')

Arguments for the function : 
1 - pivot column. This is the first column which the data will be pivoted around

2 - data to process from a query. This is the query that will produce the data to be cross tabbed

3 - pivot columns These are the headers that we want to be produced in the output table

4 - data column name .. the column name of the column that contains the headers that will be produced

5 - sum_value.. This is the column that has to summed to produce the values

6 - null value .. use null to display nothing or 0 if you want zeros .. This controls weather we want NULL or zero
producedby the crosstab query where column values are null or 0 (But you can use any other value for this ...)
 

Example:

Table (art):
id |   art    | tgl | qty
----+----------+-----+----- 1 | 508301   | XL  |   2 2 | 508301   | L   |  10 3 | 508301   | XL  |  36 4 | 5001001  | M
 |  12 5 | 5001001  | XXL |  25 6 | 45370104 | S   |  10
 

This result we want is this:  art    | S  | M  | L  | XL | XXL
----------+----+----+----+----+-----5001001  |  0 | 12 |  0 |  0 |  2545370104 | 10 |  0 |  0 |  0 |   0508301   |  0 |
0 | 10 | 38 |   0
 


This is how we will call the transformstr function

SELECT transformstr('art','SELECT * FROM art','S;M;L;XL;XXL;','tgl','qty', '0');

This will produce a string that you can copy and paste and will give you the above result.

This is an example:

SELECT art,sum("S") as "S",sum("M") as "M",sum("L") as "L",sum("XL") as "XL",sum("XXL") as "XXL" FROM (SELECT art,case
whentgl='S' then sum(qty) else 0 end as "S",case when tgl='M' then sum(qty) else 0 end as "M",case when tgl='L' then
sum(qty)else 0 end as "L",case when tgl='XL' then sum(qty) else 0 end as "XL",case when tgl='XXL' then sum(qty) else 0
endas "XXL" from (SELECT * from art) as foo GROUP BY art,tgl) as bar group by art
 


You can also get the data back by calling:

SELECT * from transform('art', 'SELECT * from art','S;M;L;XL;XXL;','tgl','qty', '0') as foo(art varchar, S numeric, M
numeric,L numeric, XL numeric, XXL numeric);
 

You will have to fiddle around a bit with the return types of the columns of the new table but it should not be
difficutto find the correct ones out ..
 

You are not obbliged to extract all of the columns of the tgl column. For example: if you need a crosstab with only th
SM and L columns you can call the function with
 

SELECT * from transform('art', 'SELECT * from art','S;M;L;','tgl','qty', '0') as foo(art varchar, S numeric, M numeric,
Lnumeric);
 

If you want nulls in the place of the 0 (zeros) change the '0' to 'null'

I you need to respect case sensitivity simply put double quotes around the parameters that have to be of a certain
case:

eg:  The table name hase to be ARTICLES, then you will call the function with doublequets around the table name:

SELECT * from transform('"ARTICLES"', 'SELECT * from art','S;M;L;XL;XXL;','tgl','qty', '0') as foo(art varchar, S
numeric,M nu        meric, L numeric, XL numeric, XXL numeric);
 

If you want that the column names in the returned table have a certain case then add the double quotes in the data
assigningpart of the function. example:
 

SELECT * from transform('art', 'SELECT * from art','S;M;L;XL;XXL;','tgl','qty', '0') as foo(art varchar, "S" numeric,
"M"nu        meric, "L" numeric, "XL" numeric, "XXL" numeric);
 

The result will be a table with S M L XL and XXL as column headers and not s m l xl andd xxl ..

*/

create or replace function transform(text,text,text,text,text,text) returns setof record as '
declarer record; --record type for et returning pivot_col alias for $1; -- What column must we pivot on?qry_data alias
for$2; -- query that contains data ..headers alias for $3; -- headers to use ..hd_col_name alias for $4; -- column of
originaltable that contain the headers of the new table ..sum_val alias for $5; -- what to sum upnullval alias for $6;
--what to use as null value ..inner_sql text;  -- internal sql queryouter_sql text; -- external sql queryheaders_len
int;-- length of the headers stingheaders_cnt int; -- this is the counter we use while going through the headers csv
stringtestint; -- only for testing purposes..headers_separator text; --what separator to use for the csv of the headers
(default:";" (semicolon))cur_separator text; -- This is the current position in the headers csv stringlast_separator
int;-- this is to remember where the last separator was (the last ;)col_val text; -- this is where we store the value
ofthe column that has to become the header of the new table. sql_string text; -- this is the whole sql string that will
producethe result of the crosstab query
 
beginheaders_cnt := 1;test := 0;headers_separator := '';''; -- this is the separator of the pivot columns
..cur_separator:= 0; -- initializelast_separator := 0; -- initialize inner_sql := ''''; -- initializeouter_sql := '''';
--initializesql_string:= ''''; --initialize-- ---------------- BUILD SQL STRINGS ------------------------------ Split
thecsv names of cols ..-- Length of string headers_len := length(headers);while headers_cnt <= headers_len loop    --
ifthe next character is ; then proceed     if substr(headers, headers_cnt, 1) = headers_separator then        -- we
havea ";" so lets start        -- build the first part of inner_sql (the case whens ..)        -- store the name of the
headercol ..        col_val := substr(headers,last_separator + 1, headers_cnt -1 - last_separator);        -- inner_sql
:=inner_sql || col_val || '' '';        inner_sql := inner_sql || ''case when '' || hd_col_name || ''='' || chr(39) ||
col_val|| chr(39) || '' then '';        inner_sql := inner_sql || ''sum('' || sum_val  ||  '') else '';
inner_sql:= inner_sql || nullval || '' end as '' || chr(34) || col_val || chr(34) || '','';
 
        -- build the outer_sql .. the second query to actually group everything up        outer_sql := outer_sql ||
''sum(''||chr(34) || col_val || chr(34) || '') as '' || chr(34) || col_val || chr(34) || '','';                --
rememberposition of previous ";"        last_separator := headers_cnt ;    end if;    -- increment the counter ..
headers_cnt:= headers_cnt + 1;end loop;-- -------------------INNER SQL------------------------------ remove the last
commafrom the end of the cases ..inner_sql := trim(trailing '','' from inner_sql);-- now build the actual SQL
stringinner_sql:= ''SELECT '' || pivot_col || '','' || inner_sql || '' from '';inner_sql := inner_sql || ''('' ||
qry_data|| '') as foo GROUP BY '' || pivot_col || '','' || hd_col_name;-- -------------------INNER
SQL-------------------------------------------------OUTER SQL------------------------------ trim the las comma from
outer_sqlouter_sql:= trim(trailing '','' from outer_sql);-- build the stringouter_sql := ''SELECT '' || pivot_col ||
'',''|| outer_sql || '' FROM ('';-- -------------------OUTER SQL------------------------------ -----------------FULL
SQLSTRING----------------------------sql_string := sql_string || outer_sql || inner_sql;sql_string := sql_string || '')
asbar group by '' || pivot_col;-- -----------------FULL SQL STRING----------------------------
 
-- ---------------- BUILD SQL STRINGS ----------------------------
----------- PERFORM THE CREATED QUERY STRING --------------for r in execute sql_string || '';'' loop    return next
r;endloop;return;
 
end; 
' language 'plpgsql';


create or replace function transformstr(text,text,text,text,text,text) returns text as '
declarerec record; --record type for et returning pivot_col alias for $1; -- What column must we pivot on?qry_data
aliasfor $2; -- query that contains data ..headers alias for $3; -- headers to use ..hd_col_name alias for $4; --
columnof original table that contain the headers of the new table ..sum_val alias for $5; -- what to sum upnullval
aliasfor $6; -- what to use as null value ..inner_sql text;  -- internal sql queryouter_sql text; -- external sql
queryheaders_lenint; -- length of the headers stingheaders_cnt int; -- this is the counter we use while going through
theheaders csv stringtest int; -- only for testing purposes..headers_separator text; --what separator to use for the
csvof the headers (default: ";" (semicolon))cur_separator text; -- This is the current position in the headers csv
stringlast_separatorint; -- this is to remember where the last separator was (the last ;)col_val text; -- this is where
westore the value of the column that has to become the header of the new table. sql_string text; -- this is the whole
sqlstring that will produce the result of the crosstab query
 
beginheaders_cnt := 1;test := 0;headers_separator := '';''; -- this is the separator of the pivot columns
..cur_separator:= 0; -- initializelast_separator := 0; -- initialize inner_sql := ''''; -- initializeouter_sql := '''';
--initializesql_string:= ''''; --initialize-- ---------------- BUILD SQL STRINGS ------------------------------ Split
thecsv names of cols ..-- Length of string headers_len := length(headers);while headers_cnt <= headers_len loop    --
ifthe next character is ; then proceed     if substr(headers, headers_cnt, 1) = headers_separator then        -- we
havea ";" so lets start        -- build the first part of inner_sql (the case whens ..)        -- store the name of the
headercol ..        col_val := substr(headers,last_separator + 1, headers_cnt -1 - last_separator);        -- inner_sql
:=inner_sql || col_val || '' '';        inner_sql := inner_sql || ''case when '' || hd_col_name || ''='' || chr(39) ||
col_val|| chr(39) || '' then '';        inner_sql := inner_sql || ''sum('' || sum_val  ||  '') else '';
inner_sql:= inner_sql || nullval || '' end as '' || chr(34) || col_val || chr(34) || '','';
 
        -- build the outer_sql .. the second query to actually group everything up        outer_sql := outer_sql ||
''sum(''||chr(34) || col_val || chr(34) || '') as '' || chr(34) || col_val || chr(34) || '','';                --
rememberposition of previous ";"        last_separator := headers_cnt ;    end if;    -- increment the counter ..
headers_cnt:= headers_cnt + 1;end loop;-- -------------------INNER SQL------------------------------ remove the last
commafrom the end of the cases ..inner_sql := trim(trailing '','' from inner_sql);-- now build the actual SQL
stringinner_sql:= ''SELECT '' || pivot_col || '','' || inner_sql || '' from '';inner_sql := inner_sql || ''('' ||
qry_data|| '') as foo GROUP BY '' || pivot_col || '','' || hd_col_name;-- -------------------INNER
SQL-------------------------------------------------OUTER SQL------------------------------ trim the las comma from
outer_sqlouter_sql:= trim(trailing '','' from outer_sql);-- build the stringouter_sql := ''SELECT '' || pivot_col ||
'',''|| outer_sql || '' FROM ('';-- -------------------OUTER SQL------------------------------ -----------------FULL
SQLSTRING----------------------------sql_string := sql_string || outer_sql || inner_sql;sql_string := sql_string || '')
asbar group by '' || pivot_col;-- -----------------FULL SQL STRING----------------------------
 
-- ---------------- BUILD SQL STRINGS ----------------------------return sql_string;
end; 
' language 'plpgsql';