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