So I love a good challenge, and my first thought here was...how can we do this without loops or XML? Here is a solution that appears to work...let me know what you think! I give myself an A+ for creativity at least : )
This SQL uses a recursive CTE to build a comma-delimited list and ensure we only get back the final results of the work. It is not as efficient at the moment as the XML solution (I didn't take time to optimize it), but certainly we could do more to make it speedier:
;WITH CTE_ITEM_NUMBERS AS (
SELECT
ROW_NUMBER() OVER (ORDER BY #t2.Item_no) AS row_num,
#t2.Item_no,
#t2.format,
1 AS level
FROM #t2 ),
CTE_RECURSIVE_ITEMS AS (
SELECT
CTE_ITEM_NUMBERS.row_num,
CTE_ITEM_NUMBERS.Item_no,
CAST(CTE_ITEM_NUMBERS.format AS VARCHAR(MAX)) AS all_formats,
CTE_ITEM_NUMBERS.level
FROM CTE_ITEM_NUMBERS
UNION ALL
SELECT
CTE_ITEM_NUMBERS.row_num,
CTE_ITEM_NUMBERS.Item_no,
CAST(CTE_ITEM_NUMBERS.format AS VARCHAR(MAX)) + ', ' + CAST(CTE_RECURSIVE_ITEMS.all_formats AS VARCHAR(MAX)) AS all_formats,
CTE_RECURSIVE_ITEMS.level + 1
FROM CTE_ITEM_NUMBERS
INNER JOIN CTE_RECURSIVE_ITEMS
ON CTE_RECURSIVE_ITEMS.Item_no = CTE_ITEM_NUMBERS.Item_no
WHERE CTE_ITEM_NUMBERS.row_num <> CTE_RECURSIVE_ITEMS.row_num
AND CTE_RECURSIVE_ITEMS.row_num > CTE_ITEM_NUMBERS.row_num
)
SELECT
*
FROM CTE_RECURSIVE_ITEMS
WHERE level = (SELECT MAX(level) FROM CTE_RECURSIVE_ITEMS REC2 WHERE REC2.Item_no = CTE_RECURSIVE_ITEMS.Item_no)
ORDER BY CTE_RECURSIVE_ITEMS.Item_no, CTE_RECURSIVE_ITEMS.row_num