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, [EMAIL PROTECTED] 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';
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
