Re: big un stacking query - help save me from myself

On Mar 14, 2013, at 10:27 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> 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
)
;