Re: How to join table to itself N times?

Okay, how about this (table names shortened):

create table m (d varchar(255) not null, v varchar(255) not null);
insert into m (d, v) values ('geography', 'north'), ('geography',
'south'), ('industry type', 'retail'), ('industry type',
'manufacturing'), ('industry type', 'wholesale');

WITH RECURSIVE t(combo, n) AS (
WITH dims AS (SELECT DISTINCT d, row_number() OVER () AS n FROM m GROUP BY d)
SELECT '{}'::text[], 1
UNION ALL
SELECT array_append(t2.combo::text[], m.v::text), t2.n+1
FROM t t2, dims
CROSS JOIN m
WHERE m.d = dims.d AND dims.n = t2.n
)
SELECT *
FROM t
WHERE n = (SELECT COUNT(DISTINCT d) + 1 FROM m);

Gives these results:

combo | n
-----------------------+---
{retail,north} | 3
{manufacturing,north} | 3
{wholesale,north} | 3
{retail,south} | 3
{manufacturing,south} | 3
{wholesale,south} | 3
(6 rows)

Paul

On Wed, Mar 20, 2013 at 8:40 PM, Paul Jungwirth
<pj(at)illuminatedcomputing(dot)com> wrote:
> 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.

--
_________________________________
Pulchritudo splendor veritatis.