On Mar 14, 2013, at 10:27 AM, Kevin Grittner <kgrittn@ymail.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
numberof columns. There are some colums that I am leaving alone (those are the var1, var2, var3 variables) and a
limitednumber 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
amtrying here. I let the full version of the below query run for 2 days before killing it when it threatened to fill
theentire 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
)
;