Creating 'Groups' of Records

[jmcclain]

Here's a doozy that I could use some help with.

My table has the following two columns:
UserID
TransactionID

Each user ID may have an unlimited number of TransactionIDs and there may be an unlimited number of different TransactionIDs.

For example:
UserID TranID
UserA XXX1
UserA XXX2
UserB XXX3
UserC XXX1
UserC XXX2

What I need to show is the different combinations of TranIDs user's have.

TranIDs
XXX1
XXX2
Users with this combination of TranIDs
UserA
UserC
XXX3
Users with this combination of TranIDs
UserB

Basically I need to create a dynamic group for each combination of TranIDs that exist in the table. This will eventually end up on a report

I tried concatenating the tranIDs into one field, that somewhat worked, but I'm having problems grouping it as the field is excessively long (1000+char).

Any ideas on how to do this?

 

[BNPMike]

I'll wager you can't do this with SQL. Anyone cleverer than me care to disagree?

 

[Golom]

BNPMike is right ... no way ... and you should be glad there isn't.

If you had only 10 unique TransIDs there are 1,023 possible combinations.

In general, the number of combinations is (2 ^ n) - 1 where "n" is the number of distinct TransID values.

20 TransIDs would give you 1,048,575 combinations,
50 would be 1,125,899,906,842,623