SQL Unique Rows from single table

Question

Here is my table:

family  symbol
1   A
1   B
1   C
2   D
2   F
2   E

The result I want is 2 rows: 1,A,B,C and 2,D,E,F. The order doesn't matter.

This:

SELECT T.family, T.symbol,T1.Symbol, T2.symbol
From T
    inner join T as T1 on T.family = T1.family
    inner join T as T2 on T.family = T2.family
WHERE T.symbol <> T1.symbol
    AND T.symbol <> T2.symbol
    AND T1.symbol <> T2.symbol

Gives this:

family  symbol  Symbol  symbol
1   A   C   B
1   A   B   C
1   B   C   A
1   B   A   C
1   C   B   A
1   C   A   B
2   D   E   F
2   D   F   E
2   F   E   D
2   F   D   E
2   E   F   D
2   E   D   F

The actual table has multiple family values, each with a variable number of symbols.

Answer 1

What you are looking for is a pivot table. Take a look at

http://msdn.microsoft.com/en-us/library/ms177410.aspx

See if you can apply your logic to a pivot and that should give you the results you are wanting.

Answer 2

Try this:

select family, group_concat(symbol separator ',')
  from T
 group by family

It's not exactly what you want, but maybe it's close enough. It will concatenate all different symbols for each family into a single column. Query is simple and you don't need to use OLAP or any other complex SQL construction.

Answer 3

There's a complete implementation of GROUP_CONCAT for Informix in Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list). It will certainly handle the data you show.