SQL Get list of record combinations from related key

Question

I need to get a list of possible combinations of a MIX field from a table structure (which, I will note, is itself derived from several select statements) that looks like the following:

#:| [key1],  [key2],  [Key3],  [MIX]
   ------------------------------------------
1 |  001      0        20       m1
2 |  001      0        20       c1
3 |  001      0        20       a1
4 |  002      0        30       m1
5 |  002      0        30       c1
6 |  002      1        10       m1
7 |  002      1        10       c1
8 |  002      1        10       a1
9 |  002      1        40       m1
10|  002      1        40       c2

where the list of resulting distinct combinations of MIX records by key fields would be:

1| m1 c1 a1
2| m1 c1
3| m1 c2

I don't care to much what the output format looks like at this point, but I will later be using it to go back and certain records.

I already have the script to get the table above, but I'm scrathing my head as to how to get this.

Note: I'm using Microsoft SQL Server Managment Studio (so T-SQL is all fair game). I wouldn't mind doing this analysis in vba either, but I'm sure their's a simpler way to do it in SQL that I'm just not seeing.

Answer 1

You may notice the Top 1000 in NEWMIX. This was to keep what appears to be your desired sequence

Example

;with cte as (
    Select Distinct 
           [Key] = concat(key1,key2,key3)
          ,mix 
    from YourTable
)
Select Distinct
       NewMix = Stuff((Select ' ' +mix From (Select Top 1000 Mix from cte Where [Key]=A.[Key] Order by Mix Desc) S For XML Path ('')),1,1,'') 
 From cte A

Returns

NewMix
m1 c1
m1 c1 a1
m1 c2

EDIT - If the sequence is not important

NewMix = Stuff((Select ' ' +mix From cte Where [Key]=A.[Key] For XML Path ('')),1,1,'')