Multiple Combinations of Values in Numerical Order

Question

I have searched and attempted to solve this puzzle myself (I've gotten close, but I've had no luck). I have a large table of values (composed of Sets of Values) that can have multiple combinations, but those combinations must be returned in the ID order.

I have not been able to get this to work in SQL.

Example Set:

(Sorry I am not able to post an image which would explain it better so Ill keep it simple.)

Table[(ID, Value) {(1,A),(1,B),(1,C),(2,D),(3,F),(3,G), (4,J), (5,S),(5,T),(5,U))}

RESULTS
ID   VALUE

1   A
2   F
3   G
4   J
5   S

1   A
2   F
3   G
4   J
5   T

1   A
2   F
3   G
4   J
5   U

1   A
2   F
3   H
4   J
5   S

1   A
2   F
3   H
4   J
5   T

1   A
2   F
3   H
4   J
5   U

1   B
2   F
3   G
4   J
5   S

1   B
2   F
3   G
4   J
5   T

1   B
2   F
3   G
4   J
5   U

1   B
2   F
3   H
4   J
5   S

1   B
2   F
3   H
4   J
5   T

1   B
2   F
3   H
4   J
5   U

1   C
2   F
3   G
4   J
5   S

1   C
2   F
3   G
4   J
5   T

1   C
2   F
3   G
4   J
5   U

1   C
2   F
3   H
4   J
5   S

1   C
2   F
3   H
4   J
5   T

1   C
2   F
3   H
4   J
5   U

Sorry, this is very unclear. Please rewrite to explain more clearly what you are trying to accomplish and what you don't understand. — Jim Garrison, Apr 10 '15 at 04:10
I believe it's like this. Imagine the ID as "seats". Each row says which seat a person( the letter) can sit in. So A,B, and C can sit in seat 1. D in seat 2. Now find all possible combinations — Stephan, Apr 10 '15 at 22:40
Stephan you are correct sir. Thank oyu guys and I apologize for not being more clear, I created an image of a spreadsheet example but did not have the ability to upload images. The link Dannyg9090 provided gave me the foundation of what I was searching for. — David McMinoway, Apr 11 '15 at 19:38

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

I think this has been answered before here:

How to generate all possible data combinations in SQL?

difference being that they essentially dropped the ID column, should be easy to pull it through though.

edited May 23 '17 at 12:06

Community

1
1

answered Apr 10 '15 at 10:21

Dannyg9090

196
8

Danny this worked. Thank you and everyone for helping me out. I very much appreciate the help. – David McMinoway Apr 11 '15 at 19:34

Stephan · Accepted Answer · 2015-04-12T05:40:05.860

Here's the problem in dynamic SQL without any cursors or loops.

IF OBJECT_ID('yourTable') IS NOT NULL
    DROP TABLE yourTable;

CREATE TABLE yourTable (ID INT, Value CHAR(1));

INSERT INTO yourTable
VALUES  (1,'A'),(1,'B'),(1,'C'),
        (2,'D'),
        (3,'F'),(3,'G'),
        (4,'J'),
        (5,'S'),(5,'T'),(5,'U');

DECLARE @row_number_cols    VARCHAR(MAX),
        @Aliased_Cols       VARCHAR(MAX),
        @Cross_Joins        VARCHAR(MAX),
        @Unpivot            VARCHAR(MAX);


SELECT  @row_number_cols =  COALESCE(@row_number_cols + ',','') + col,
        @Aliased_Cols =     COALESCE(@Aliased_Cols + ',','') + CONCAT(col,' AS col',ID),
        @Cross_Joins =      COALESCE(@Cross_Joins,'') + CASE
                                                            WHEN ID = 1 THEN CONCAT(' FROM (SELECT * FROM yourTable WHERE ID = 1) AS ID',ID)
                                                            ELSE CONCAT(' CROSS JOIN (SELECT * FROM yourTable WHERE ID = ',ID,') AS ID',ID)
                                                        END,
        @Unpivot = COALESCE(@Unpivot + ',','') + CONCAT('col',ID)
FROM yourTable A
CROSS APPLY (SELECT CONCAT('ID',ID,'.Value')) CA(col) --Just so I can reuse "col" in my code
GROUP BY A.ID,CA.col

SELECT @row_number_cols,@Aliased_Cols,@Cross_Joins,@Unpivot
SELECT
'WITH CTE_crossJoins
AS
(
SELECT  ROW_NUMBER() OVER (ORDER BY ' + @row_number_cols + ') group_num,' + @Aliased_Cols +
@Cross_Joins + '
)

SELECT  group_num,
        val
FROM CTE_crossJoins
UNPIVOT
(
    val for col IN (' + @Unpivot + ')
) unpvt
ORDER BY 1,2'

Results:

group_num            val
-------------------- ----
1                    A
1                    D
1                    F
1                    J
1                    S
2                    A
2                    D
2                    G
2                    J
2                    S
3                    A
3                    D
3                    G
3                    J
3                    T
4                    A
4                    D
4                    F
4                    J
4                    T
5                    A
5                    D
5                    F
5                    J
5                    U
6                    A
6                    D
6                    G
6                    J
6                    U
7                    B
7                    D
7                    G
7                    J
7                    S
8                    B
8                    D
8                    F
8                    J
8                    S
9                    B
9                    D
9                    F
9                    J
9                    T
10                   B
10                   D
10                   G
10                   J
10                   T
11                   B
11                   D
11                   G
11                   J
11                   U
12                   B
12                   D
12                   F
12                   J
12                   U
13                   C
13                   D
13                   F
13                   J
13                   S
14                   C
14                   D
14                   G
14                   J
14                   S
15                   C
15                   D
15                   G
15                   J
15                   T
16                   C
16                   D
16                   F
16                   J
16                   T
17                   C
17                   D
17                   F
17                   J
17                   U
18                   C
18                   D
18                   G
18                   J
18                   U

Thank you Stephan. You understood what I was trying to accomplish. The link Dannyg9090 provided works and I created a cursor and loop to put them in rows rather than columns. Looking at this I think I can use your code as an example to do the same thing - build Dynamic SQL that does multiple CROSS JOINS (similar with the link Danny gave) and then UNPIVOT which will put them in rows. And it looks much cleaner than what I have. — David McMinoway, Apr 11 '15 at 19:51
I can do this in dynamic SQL without Loops or Cursors as there is no need for them and whenever possible you want to avoid them. — Stephan, Apr 12 '15 at 05:16

score -1 · Answer 3 · answered Apr 10 '15 at 09:30

-1

You can employ the SQL windows function to achieve this.

;WITH CTE AS
    (
    SELECT Id, 
    Value, 
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) RN 
    FROM Tbl
    )

SELECT * FROM CTE ORDER BY RN, ID, VALUE

Fiddle

answered Apr 10 '15 at 09:30

SouravA

5,147
2
24
49

I'm not sure this is what David is looking for. I think he wants to build every possible combination of the 5 IDs given the values they have. – Dannyg9090 Apr 10 '15 at 10:19

Multiple Combinations of Values in Numerical Order

3 Answers3