Combine values from same column

Question

I have a table with the following structure:

Type    Value
A       abc
A       def
A       xyz

I want a result set that will be all possible combinations of the "Value" but should be distinct as well.

Result Set:

Value
abc def
abc xyz
def xyz
abc def xyz

If the result set has "abc def", I don't want "def abc" to show up. Even though these are 2 distinct values, these are duplicates for me. I'm stuck on 2 things:

How to eliminate duplicates?
In the table structure above, I had one type "A" with 3 values. The same table will have multiple types and each type will have different number of values. For instance, I will have Type "B" with say 4 values. How do I figure out how many times I need to iterate through each type.

Any help will be appreciated.

Tag your question with the database you are using. This logic requires hierarchical queries or recursive CTEs, which are not fully standardized across databases. Plus, a query returns a fixed set of columns, so you can only get the combinations as a single string or array, not as different columns (unless you know the maximum number up-front). — Gordon Linoff, Jul 01 '19 at 17:08
Plus. if you're using a procedural language on the front-end, you might find [this post](https://stackoverflow.com/questions/1952153/what-is-the-best-way-to-find-all-combinations-of-items-in-an-array) helpful in generating all possible combinations from a set of values (see the highest voted answer). Just fetch your sets from the DB and then do combinatorics on the front-end. — dotNET, Jul 01 '19 at 17:10
We are using microsoft sql server. I want the result set to be multiple rows of the same column. There is no front-end for this. — Rahul, Jul 01 '19 at 17:12

score 1 · Accepted Answer · answered Jul 01 '19 at 18:12

Try this following query-

WITH CTE (id,Value)
AS
(
    SELECT DISTINCT 1,Value 
    FROM your_table 
)


SELECT A.Value + ' '+ B.Value  AS Value
FROM CTE A 
INNER JOIN CTE B ON A.id = B.id
WHERE (A.Value <> B.Value AND A.Value > B.Value) 

UNION ALL

SELECT A.Value + ' '+ B.Value  + ' '  + C.Value AS Value
FROM CTE A 
INNER JOIN CTE B ON A.id = B.id
INNER JOIN CTE C ON A.id = C.id
WHERE 
(
    A.Value  <>B.Value AND A.Value  >B.Value 
    AND A.Value <> C.Value AND  A.Value > C.Value 
    AND B.Value <> C.Value AND B.Value > C.Value
)

Combine values from same column

1 Answers1