Re: How to join table to itself N times?

Wow, this is a fun puzzle. I'd love to be the first to solve it with
just SQL, but I don't have a solution yet. Here are some elements that
might be useful:

SELECT market_segment_dimension, array_agg(value)
FROM market_segment_dimension_values
GROUP BY market_segment_dimension;

the UNNEST function
the ROW function
window functions like row_number and nth_value
the crosstab function (requires installing an extension; this seems
like cheating if you ask me)

Good luck!
Paul

On Wed, Mar 20, 2013 at 7:14 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Wed, Mar 20, 2013 at 5:38 PM, W. Matthew Wilson <matt(at)tplus1(dot)com> wrote:
>> I got this table right now:
>>
>> select * from market_segment_dimension_values ;
>> +--------------------------+---------------+
>> | market_segment_dimension | value |
>> +--------------------------+---------------+
>> | geography | north |
>> | geography | south |
>> | industry type | retail |
>> | industry type | manufacturing |
>> | industry type | wholesale |
>> +--------------------------+---------------+
>> (5 rows)
>>
>> The PK is (market_segment_dimension, value).
>>
>> The dimension column refers to another table called
>> market_segment_dimensions.
>>
>> So, "north" and "south" are to values for the "geography" dimension.
>>
>> In that data above, there are two dimensions. But sometimes there could be
>> just one dimension, or maybe three, ... up to ten.
>
> If the number of dimensions is not fixed, then you'll probably have to
> write a plpgsql function to first interrogate the data set for how
> many dimensions there are and then to build an n-dimension query.
> While joining a variable number of tables may be problematic as you
> won't have a fixed number of columns, using a union might give you
> what you want with a fixed number of columns.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
_________________________________
Pulchritudo splendor veritatis.