On Mar 14, 2013, at 10:27 AM, Kevin Grittner <kgrittn@xxxxxxxxx> wrote: >> > > I didn't understand your description of what you are trying to do, > and the example has so many columns and cases that it would take a > long time to understand it. Can you distill this down to just a > few columns and cases so that it is easier to understand what you > are trying to accomplish? Even better would be a self-contained > test case with just a few rows so people can see "before" and > "after" data. What you have already posted will help give context > on how it needs to scale, which is important, too; but if you make > the issue easier to understand, the odds improve that someone will > volunteer the time needed to make a suggestion. Here is a simplified version of the query approach I am attempting. First of all, this query works fine with a limited number of columns. There are some colums that I am leaving alone (those are the var1, var2, var3 variables) and a limited number of variables that I am trying to "unstack" (those are unstack1, unstack2, unstack3…. variables). The problem lies in that the real table I am working with is vary large. There are 30 plus var1, var2… columns I am not unstacking, and 30 plus variables (unstack1, unstack2…) that I am unstacking, from a 25 million row table. I have looked at the tablefunc approach, and I am wondering if it is any more efficient than using the CASE approach I am trying here. I let the full version of the below query run for 2 days before killing it when it threatened to fill the entire hard drive (250 Gigs). CREATE TABLE unstacked_table AS ( SELECT var1, var2, var3, MAX ( CASE WHEN variable_name = 'unstack1' THEN VALUE END ) AS unstack1, MAX ( CASE WHEN variable_name = 'unstack2' THEN VALUE END ) AS unstack2, MAX ( CASE WHEN variable_name = 'unstack3' THEN VALUE END ) AS unstack3, MAX ( CASE WHEN variable_name = 'unstack4' THEN VALUE END ) AS unstack4 FROM stacked_table GROUP BY variable1, variable2, variable3 ) ; -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
