Re: Exploding information to flatness

If the number of groups is limited you can do something like:

select user.name

      ,max(case when member.user = user.id and member.group=1 then group.name else null end) as "group[1]"

      ,max(case when member.user = user.id and member.group=2 then group.name else null end) as "group[2]"

      ,max(case when member.user = user.id and member.group=3 then group.name else null end) as "group[3]"

      ,max(case when member.user = user.id and member.group=4 then group.name else null end) as "group[4]"
  from user, group, member

         where  group.id=member.group
       group by user.name

It would be quite easy to generate above SQL by reading the group table or by using a stored procedure.

In DB2 this results in: (not sure how/if it works in other RDMS products)

NAME group[1] group[2] group[3] group[4] ---------- ---------- ---------- ---------- ----------

user1      group1     group2     -          group4
user2      -          -          group3     group4
user3      -          group2     group3     group4

Todd Benson wrote:

> Is there an acceptable way to 'blow' out information into a flat file
> from tables that are related? In this particular case, I'm trying to
> move related data from tables to an Excel spreadsheet. Here would be
> a simplified example:
>
> create table user (
> id int primary key,
> name varchar(50)
> )
>
> create table group (
> id int primary key,
> name varchar(50)
> )
>
> create table member (
> user int,
> group int,
> primary key( user, group )
> )
>
> Obviously, 'user' to 'group' is a many-many relationship.
>
> I want to rotate out the groups into 'apparent' attributes for each
> user row in the spreadsheet. In other words, I want my rows from my
> select statement to look like this with 4 to n columns:
>
> user.name group[1].name group[2].name group[3].name ...
>
> where group[1], group[2], and group[3] are columns for all the groups
> that exist, but will have values of NULL for which the user is not a
> member.
>
> Anyone have experience with this?
>
> Thanks,
> Todd

--
Anton Versteeg
DB2 Specialist
IBM Netherlands