Re: Unpivot / uncrosstab support? - Mailing list pgsql-general

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Josh.Vote@csiro.au
> Sent: Thursday, 13 May 2010 10:41 AM
> To: pgsql-general@postgresql.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
spottedthe 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
solutionbut 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
thatyour 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.
Italso 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
columnvalues 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