Re: Exploding information to flatness

Yes, this is exactly what I needed to do. Thank you very much.

Todd

Anton Versteeg <Anton_Versteeg_at_nl.ibm> wrote in message news:<3CBFE36B.858FBEA5_at_nl.ibm>...
> 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
Received on Fri Apr 19 2002 - 17:59:21 CEST