Can a select statement include nested result-sets?
No. This isn't really possible.
SQL Server has no support for nested relations and NF²
Though you could use FOR XML PATH to bring it back in a hierarchical manner.
SELECT ID AS [@ID], Customer AS [@Customer], (SELECT ID AS [@ID], OrderID AS [@OrderID], Product AS [@Product] FROM OrderItems WHERE OrderItems.OrderID = o.ID FOR XML PATH('OrderItems'), TYPE)FROM Orders oFOR XML PATH('Order'), ROOT('Orders') Returns
<Orders> <Order ID="1" Customer="John"> <OrderItems ID="1" OrderID="1" Product="Guitar" /> <OrderItems ID="2" OrderID="1" Product="Bass" /> </Order> <Order ID="2" Customer="James"> <OrderItems ID="3" OrderID="2" Product="Guitar" /> <OrderItems ID="4" OrderID="2" Product="Drums" /> </Order> </Orders>This doesn't repeat the parent Orders