Concatenate all combinations

Question

Suppose I have a table with 3 rows: A, B, C

DECLARE @t TABLE (col varchar(1))
INSERT @t 
    SELECT 'A' UNION
    SELECT 'B' UNION
    SELECT 'C'

I'd like to concatenate the three values an select all possible combinations:

A;B;C;
A;C;B;
B;A;C;
B;C;A;
C;A;B;
C;B;A;

This query gives me the first combination: A;B;C;

SELECT STUFF(
(
    SELECT t1.col + ';'
    FROM @t AS t1
    --CROSS JOIN @t AS t2
    FOR XML PATH('')
),1,0,'') AS List

If I uncomment the line with the CROSS JOIN I get: A;B;C;A;B;C;A;B;C;

But obvisiously that's not what I'm looking for ;-) First of all, it's all in one row. An second, it's alsways the same combiantion (A;B;C;, A;B;C;, A;B;C;) whereas I'm looking for A;B;C;, B;C;A;, C;A;B;

Take a look at this. Its the exact same question because "All Combinations" are called **Permutations**. https://stackoverflow.com/questions/3621494/the-most-elegant-way-to-generate-permutations-in-sql-server — Strawberryshrub, May 03 '19 at 09:53
What's the expected result of someone suddenly inserts the value `D` into the table? — jarlh, May 03 '19 at 09:54

score 2 · Answer 1 · answered May 03 '19 at 09:49

2

This is not a very generalistic approach (you didn't specify your dbms, tested in sql server), but does return all combinations:

DECLARE @t TABLE (col varchar(1))
INSERT @t 
    SELECT 'A' UNION
    SELECT 'B' UNION
    SELECT 'C'


SELECT  CONCAT(T.col, ';', T2.col, ';', T3.col, ';') AS list
FROM    @t AS T
CROSS JOIN @t AS T2
CROSS JOIN @t AS T3 
WHERE   T2.col <> T.col
    AND T3.col <> T.col
    AND T3.col <> T2.col

Returns:

+--------+
|  list  |
+--------+
| B;A;C; |
| C;A;B; |
| A;B;C; |
| C;B;A; |
| A;C;B; |
| B;C;A; |
+--------+

answered May 03 '19 at 09:49

HoneyBadger

14,750
3
34
48

Thank you for your answer! This works for exactly three rows. So eyp, that's what I was asking for... :-) What I didn't mention: I need this to work with 4 or 5 or n values as well. So, if I add a `4` this answer doesn't work. – Jan Köhler May 03 '19 at 09:55
Correct, that's what I meant with it not being a very generalistic approach. If you'd like to use this approach, I guess you'll have to use dynamic sql. – HoneyBadger May 03 '19 at 10:03
The linked duplicate has a more flexible method. – HoneyBadger May 03 '19 at 10:06

Concatenate all combinations

1 Answers1