sql - Find combinations of a column's string value

Question

SQL Server code, if possible.

Let's say you have a table with two columns. Column 1 is named Monster and Column 2 is named Level:

Monster | Level
_______________
Small Beast | 300
Large Beast | 700
Small Dragon | 350
Large Dragon | 800

How can I query this table to get All the possible combinations of Column 1:Monster? Keep in mind that the number of monsters in the table can fluctuate.

So output would be:

Small Beast, Large Beast
Small Beast, Small Dragon
Small Beast, Large Dragon
Large Beast, Small Dragon
Large Beast, Large Dragon
Small Dragon, Small Beast, Large Beast
Large Dragon, Small Beast, Large Beast

... and so on.

I would then like to add the sum value from Column 2:Level for all monsters in the combination and output them like so:

Small Beast, Large Beast: 1000
Small Beast, Small Dragon: 650
Large Dragon, Small Beast, Large Beast: 1800

Take a look at CROSS JOINs for starters.

Radu Gheorghiu
– Radu Gheorghiu

2016-10-04 07:53:35 +00:00
Commented Oct 4, 2016 at 7:53 — Radu Gheorghiu
– Radu Gheorghiu, Commented Oct 4, 2016 at 7:53

gofr1 · Accepted Answer · 2016-10-04 08:40:40Z

3

You can use recursive CTE:

;WITH cte AS (
SELECT  Monster, 
        [Level],
        1 as l
FROM YourTable
UNION ALL
SELECT  c1.Monster+','+c2.Monster,
        c1.[Level]+c2.[Level],
        c1.l+1
FROM cte c1
CROSS JOIN YourTable c2
WHERE c1.Monster NOT LIKE '%'+c2.Monster+'%'
)


SELECT *
FROM cte
ORDER BY l
OPTION (MAXRECURSION 0)

Output:

Monster                                             Level   l
Small Beast                                         300     1
Large Beast                                         700     1
Small Dragon                                        350     1
Large Dragon                                        800     1
Large Dragon,Small Beast                            1100    2
Large Dragon,Large Beast                            1500    2
Large Dragon,Small Dragon                           1150    2
Small Dragon,Small Beast                            650     2
Small Dragon,Large Beast                            1050    2
Small Dragon,Large Dragon                           1150    2
Large Beast,Small Beast                             1000    2
Large Beast,Small Dragon                            1050    2
Large Beast,Large Dragon                            1500    2
Small Beast,Large Beast                             1000    2
Small Beast,Small Dragon                            650     2
Small Beast,Large Dragon                            1100    2
Small Beast,Large Dragon,Large Beast                1800    3
Small Beast,Large Dragon,Small Dragon               1450    3
Small Beast,Small Dragon,Large Beast                1350    3
Small Beast,Small Dragon,Large Dragon               1450    3
...
Large Beast,Small Dragon,Large Dragon,Small Beast   2150    4
Large Beast,Small Dragon,Small Beast,Large Dragon   2150    4
Small Beast,Small Dragon,Large Dragon,Large Beast   2150    4
Small Beast,Small Dragon,Large Beast,Large Dragon   2150    4
Small Beast,Large Dragon,Small Dragon,Large Beast   2150    4
Small Beast,Large Dragon,Large Beast,Small Dragon   2150    4

answered Oct 4, 2016 at 8:40

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Jamiec Over a year ago

I tried this, and had to wrap a CAST() around the c1.Monster+','+c2.Monster bit otherwise I got error Types don't match between the anchor and the recursive part in column "Monster" of recursive query "cte". Just in case the OP gets the same.

gofr1 Over a year ago

@Jamiec In YourTable I used nvarchar(max) for monster column.

Jamiec Over a year ago

Ah yes - I had used VARCHAR(500) so had to cast the result to same. You're 100% right if you use VARCHAR(MAX) there is no problem and no cast required. Great answer BTW!

Kian Over a year ago

Sadly I have no way of checking this complex of code on my own right now. But it sure looks great. I am hovering over marking this as the correct answer. Btw, do you know of any books/resources that will help me grasp the concepts in your answer? I had some sql experience in school but it's been a while and I want to do this right.

Jamiec Over a year ago

@Kian if it helps, I checked this and the other similar answer. Both do what you asked for - this one contains duplicates with different orders (Small Beast, Large Beast and Large Beast, Small Beast) whereas the other avoids that duplication. use whichever suits your needs most appropriately.

|

Artur Joshi · Accepted Answer · 2016-10-04 07:57:53Z

0

There is a way to do it:

SELECT M1.monster,
    M2.monster,
    M1.level + M2.level
FROM
    monsters AS M1
CROSS JOIN
    monsters AS M2

Result:

"Small Beast";"Small Beast";600
"Small Beast";"Large Beast";1000
"Small Beast";"Small Dragon";650
"Small Beast";"Large Dragon";1100
"Large Beast";"Small Beast";1000
"Large Beast";"Large Beast";1400
"Large Beast";"Small Dragon";1050
"Large Beast";"Large Dragon";1500
"Small Dragon";"Small Beast";650
"Small Dragon";"Large Beast";1050
"Small Dragon";"Small Dragon";700
"Small Dragon";"Large Dragon";1150
"Large Dragon";"Small Beast";1100
"Large Dragon";"Large Beast";1500
"Large Dragon";"Small Dragon";1150
"Large Dragon";"Large Dragon";1600

Best wishes!

answered Oct 4, 2016 at 7:57

Artur Joshi

757 bronze badges

5 Comments

jarlh Over a year ago

Note that 3 creatures per row are expected after a while.

geofftnz Over a year ago

add a union all with a 3-way cross join to get the 3-monster combinations.

Artur Joshi Over a year ago

@geofftnz yeah, i also have thinked about it, but you have to also union with 4,5, and so on Also it'll have different colums numbers, so it couldn't be merged via union

Kian Over a year ago

How would you show three creature combinations? What if my table had 5 monsters, how would you show five creature combinations? What I'm getting at is what if the number of creatures in the monster column fluctuate? I'm trying to figure out if it's possible.

Artur Joshi Over a year ago

As i understood, it really depends on DBMS, which you use. Those, which maintains TransactSQL(MSSQL and so on) are able to make your task. In general case, i have no, idea how to implement this

Collectives™ on Stack Overflow

sql - Find combinations of a column's string value

2 Answers 2

11 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related