Get combinations of a columns-CodePudding

I have two columns and i wanted to display all the possibilities of a single columns for ex:

Table 1

I wanted to display the combinations of each value.

I have used selfjoin, but that doesnot workout.

SELECT a.column1 FROM table1 a CROSS JOIN table1 b where column1="value"

Any suggestions would be appreciated

CodePudding user response：

Check this solution:

CREATE TABLE (Column1 VARCHAR(20), Column2 VARCHAR(20));

INSERT INTO Combination VALUES ('value', 145), ('value', 146),('value2', 13), ('value2', 56), ('value2', 364);

 --------- --------- 
| Column1 | Column2 |
 --------- --------- 
| value   | 145     |
| value   | 146     |
| value2  | 13      |
| value2  | 56      |
| value2  | 364     |
 --------- --------- 
5 rows in set (0.00 sec)

Now Full SQL Code:

WITH cte1 AS (SELECT * FROM Combination WHERE Column1 = 'value') , cte2 AS (SELECT * FROM Combination WHERE Column1 = 'value2') 
SELECT CONCAT(cte1.Column2, cte2.Column2) AS Result FROM cte1 CROSS
 JOIN cte2;

 -------- 
| Result |
 -------- 
| 14613  |
| 14513  |
| 14656  |
| 14556  |
| 146364 |
| 145364 |
 -------- 
6 rows in set (0.00 sec)

CodePudding user response：

few of them doesnot get concatinated and having this. is it possible to use case condition as well for ex:

CASE COLUMN2 ..
THEN
END...
SELECT CONCAT(cte1.Column2, cte2.Column2) AS Result FROM cte1 CROSS
 JOIN cte2;

| Result   | 
| -------- | 
| 001-     | 
| 123-     |
| 456-     |
| 789-     |