pgsql crosstab function

Hello all .. i just wrote this script in plpgsql. Basically it performs a crosstab query. The difference between this and the one you already have in contrib is that this one has no limitations on the arguments passed to it. The headers of the resulting query are passed as a csv string so it can grow without having to create a function for every crosstab query we want to execute. The functions included are 2. One returns a sql string which can then be executed and the other performs the sql from the function itself and produces output. I tested it on a couple of tables and it seems to work quite well.. All the instructions with a very very simple test are included in the .sql file. Please test it out and decide 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 tested this only in pg 7.4beta5 ..

Best Regards,

Fabrizio Mazzoni..

/*
Function by Fabrizio Mazzoni, veramente(at)libero(dot)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 crosstab query 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 produced by 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 | 25
45370104 | 10 | 0 | 0 | 0 | 0
508301 | 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 when tgl='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 end as "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 difficut to 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 S M 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, L numeric);

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 assigning part 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 '
declare
r 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 original table that contain the headers of the new table ..
sum_val alias for $5; -- what to sum up
nullval alias for $6; -- what to use as null value ..
inner_sql text; -- internal sql query
outer_sql text; -- external sql query
headers_len int; -- length of the headers sting
headers_cnt int; -- this is the counter we use while going through the headers csv string
test int; -- 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 string
last_separator int; -- this is to remember where the last separator was (the last ;)
col_val text; -- this is where we store the value of the column that has to become the header of the new table.
sql_string text; -- this is the whole sql string that will produce the result of the crosstab query
begin
headers_cnt := 1;
test := 0;
headers_separator := '';''; -- this is the separator of the pivot columns ..
cur_separator := 0; -- initialize
last_separator := 0; -- initialize
inner_sql := ''''; -- initialize
outer_sql := ''''; --initialize
sql_string := ''''; --initialize
-- ---------------- BUILD SQL STRINGS ----------------------------
-- Split the csv names of cols ..
-- Length of string
headers_len := length(headers);
while headers_cnt <= headers_len loop
-- if the next character is ; then proceed
if substr(headers, headers_cnt, 1) = headers_separator then
-- we have a ";" so lets start
-- build the first part of inner_sql (the case whens ..)
-- store the name of the header col ..
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) || '','';

-- remember position of previous ";"
last_separator := headers_cnt ;
end if;
-- increment the counter ..
headers_cnt := headers_cnt + 1;
end loop;
-- -------------------INNER SQL----------------------------
-- remove the last comma from the end of the cases ..
inner_sql := trim(trailing '','' from inner_sql);
-- now build the actual SQL string
inner_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_sql
outer_sql := trim(trailing '','' from outer_sql);
-- build the string
outer_sql := ''SELECT '' || pivot_col || '','' || outer_sql || '' FROM ('';
-- -------------------OUTER SQL----------------------------
-- -----------------FULL SQL STRING----------------------------
sql_string := sql_string || outer_sql || inner_sql;
sql_string := sql_string || '') as bar 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;
end loop;
return;
end;
' language 'plpgsql';

create or replace function transformstr(text,text,text,text,text,text) returns text as '
declare
rec 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 original table that contain the headers of the new table ..
sum_val alias for $5; -- what to sum up
nullval alias for $6; -- what to use as null value ..
inner_sql text; -- internal sql query
outer_sql text; -- external sql query
headers_len int; -- length of the headers sting
headers_cnt int; -- this is the counter we use while going through the headers csv string
test int; -- 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 string
last_separator int; -- this is to remember where the last separator was (the last ;)
col_val text; -- this is where we store the value of the column that has to become the header of the new table.
sql_string text; -- this is the whole sql string that will produce the result of the crosstab query
begin
headers_cnt := 1;
test := 0;
headers_separator := '';''; -- this is the separator of the pivot columns ..
cur_separator := 0; -- initialize
last_separator := 0; -- initialize
inner_sql := ''''; -- initialize
outer_sql := ''''; --initialize
sql_string := ''''; --initialize
-- ---------------- BUILD SQL STRINGS ----------------------------
-- Split the csv names of cols ..
-- Length of string
headers_len := length(headers);
while headers_cnt <= headers_len loop
-- if the next character is ; then proceed
if substr(headers, headers_cnt, 1) = headers_separator then
-- we have a ";" so lets start
-- build the first part of inner_sql (the case whens ..)
-- store the name of the header col ..
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) || '','';

-- remember position of previous ";"
last_separator := headers_cnt ;
end if;
-- increment the counter ..
headers_cnt := headers_cnt + 1;
end loop;
-- -------------------INNER SQL----------------------------
-- remove the last comma from the end of the cases ..
inner_sql := trim(trailing '','' from inner_sql);
-- now build the actual SQL string
inner_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_sql
outer_sql := trim(trailing '','' from outer_sql);
-- build the string
outer_sql := ''SELECT '' || pivot_col || '','' || outer_sql || '' FROM ('';
-- -------------------OUTER SQL----------------------------
-- -----------------FULL SQL STRING----------------------------
sql_string := sql_string || outer_sql || inner_sql;
sql_string := sql_string || '') as bar group by '' || pivot_col;
-- -----------------FULL SQL STRING----------------------------

-- ---------------- BUILD SQL STRINGS ----------------------------
return sql_string;
end;
' language 'plpgsql';