Create
Table product (
productid        INT,
typeid              INT,
typeDesc        VARCHAR(15) )

INSERT INTO product VALUES (1,2,‘Male’),
(1,2,‘Female’),
(1,3,‘Red’),
(1,3,‘Green’),
(1,3,‘Blue’),
(1,4,‘Small’),
(1,4,‘Medium’),
(1,4,‘Large’),
(2,5,‘Small’),
(2,5,‘Medium’),
(2,5,‘Large’)

They wanted to make a single list of all possible choices for a given product.  For example, the first several rows for product 1 would start like this:

Large, Blue, Female
Large, Blue, Male
Large, Green, Female
Large, Green, Male
Large, Red, Female
Large, Red, Male

You would see every type combination for each product.  Product 2 would only have 3 lines.  They had tried a couple of other  joins and temp tables and they asked me to take a look because the query would have to be dynamic as the number of combinations would vary from product to product.  I was sure that I could use a CTE to resolve this.  I came across this Simple Talk article that helped me get the logic straight in my head.  Although I had been using CTE in my queries, this was the first time I really used it recursively.

The logic is to declare the CTE and the query you want and the inside the CTE use the UNION statement to then call the CTE again.  It will loop through and create the string.

WITH product_CTE ( productid, typeid, typeDesc, long_answer,length)
AS (SELECT productid, typeid,CAST( typeDesc ASVARCHAR(8000)),CAST(”ASVARCHAR(8000)), 0
FROM product
WHERE productid = 1

UNION ALL

        SELECT x.productid, x.typeid,CAST(x.typeDesc +‘, ‘+ cte.typeDesc ASVARCHAR(8000)),
CAST( cte.typeDesc ASVARCHAR(8000)),length+ 1
FROM product_CTE cte
INNER JOIN (Select productid, typeid, typeDesc
FROM product) X ON cte.productid = x.productid and cte.typeid < x.typeid)

 Select *
FROM product_CTE              
WHERE length= 2
Order BY typeDesc

Now there is a catch.  The recursion will show you all the possible combinations IE (LARGE, Blue) which is incomplete.  This is where the length comes in.  They way it makes sense to me is N-1 or the number of combinations, in this case 3 for product 1, minus 1.  This example will only give the rows for a specific productid and then you have to know that number of combinations for that product.

Anyway, I thought it was cool the way it came together.  On a side note, does anyone know of a widget I can use to post code?  It is pretty tedious in the editor and still doesn’t look good.