Re: Unpivot / uncrosstab support?

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Josh(dot)Vote(at)csiro(dot)au
> Sent: Thursday, 13 May 2010 10:41 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [ExternalEmail] [GENERAL] Unpivot / uncrosstab support?
>
> Hi,
>
> I was wondering if Postgresql (8.3 or later) had an equivalent function to the MS-SQL function "unpivot"? I've spotted the user contributed crosstab which is similar to the "pivot" function, but I can't find an equivalent "uncrosstab".
>
> Essentially what I'm trying to do is take a table like this...
>
> clientID clientName contact1 contact2 contact3 contact4
> ----------- -------------------- ----------- ----------- ----------- -----------
> 1 ABC Corp 1 34 2 NULL
> 2 DEF Foundation 6 2 8 9
> 3 GHI Inc. 5 9 NULL NULL
> 4 XYZ Industries 24 NULL 6 NULL
>
> ...and get a result like this...
>
> clientID ContactNumber ContactID
> ----------- ----------------------- -----------
> 1 contact1 1
> 1 contact2 34
> 1 contact3 2
> 2 contact1 6
> 2 contact2 2
> 2 contact3 8
> 2 contact4 9
> 3 contact1 5
> 3 contact2 9
> 4 contact1 24
> 4 contact3 6
>
> Which can be accomplished with unpivot.
>
> For more info I've stolen the example from http://weblogs.sqlteam.com/jeffs/archive/2008/04/23/unpivot.aspx
>
> Thanks
> Josh Vote

Well after doing some of my own investigating I came up with my own solution, it's slow, unoptimised and specific to my solution but it works. Currently this gets dumped into a materialized view so the performance isn't a huge problem for me.

The example given is making the assumption that you are unpivoting from ONLY columns which are double precision and that your unpivoting column names are fixed. It should be easy enough to change these assumptions to your own needs...

-- This function sucks, it's hardcoded to only extract double precision columns but it should be easy enough to change. It also has pretty poor performance.
CREATE OR REPLACE FUNCTION get_column_value(text, text, text, text)
RETURNS double precision AS
$BODY$
DECLARE
val double precision := null;
rRec RECORD;
BEGIN
FOR rRec IN EXECUTE('SELECT ' || quote_ident($1) || ' AS val FROM ' || quote_ident($2) || ' WHERE ' || quote_ident($3) || ' = ' || quote_literal($4)) LOOP
val = rRec.val;
END LOOP;

RETURN val;
END;
$BODY$
LANGUAGE 'plpgsql';

-- This is the actual query, it cross joins the list of column names against the actual table and includes the actual column values too.
SELECT *
FROM
(SELECT my_table.clientid, my_columns.column_name as contact_number, get_column_value(my_columns.column_name, 'clients_table', 'clientid', my_table.clientid) AS contactid
FROM clients_table my_table
CROSS JOIN
(SELECT a.attnum, a.attname AS column_name
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = clients_table '
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
AND (a.attname = 'contact1'
OR a.attname = 'contact2'
OR a.attname = 'contact3',
OR a.attname = 'contact4')) my_columns) joined_table
WHERE contactid IS NOT NULL;

Running the query generates the normalized unpivot table.

Hope someone else finds this useful...

Josh Vote