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.marlowe@xxxxxxxxx> wrote: > On Wed, Mar 20, 2013 at 5:38 PM, W. Matthew Wilson <matt@xxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- _________________________________ Pulchritudo splendor veritatis. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
