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

Answer 1

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