question

It's tricky to know exactly which way to go without seeing the appropriate part of your schema. Assuming you need to do this in straight T-SQL, then using a [`PIVOT`][1] might be the right approach. For example: Use Tempdb GO DECLARE @MyOrderTable TABLE (OrderID int IDENTITY(1,1), OrderDate Date) DECLARE @MyOrderDetailTable TABLE (OrderID int, PartID char(5), Quantity int) INSERT INTO @MyOrderTable (OrderDate) VALUES ('2013-01-01'), ('2013-01-02'), ('2013-01-05'), ('2013-01-10') INSERT INTO @MyOrderDetailTable VALUES (1, 'PART1', 10), (2, 'PART3', 5), (2, 'PART5', 2), (3, 'PART2', 50), (4, 'PART1', 25), (4, 'PART3', 2), (4, 'PART4', 5) /* select * from @MyOrderTable select * from @MyOrderDetailTable SELECT o.OrderDate, d.OrderID, d.PartID, d.Quantity FROM @MyOrderTable o LEFT JOIN @MyOrderDetailTable d ON o.orderid = d.orderid */ SELECT Year(OrderDate) AS [Year], OrderID, [Part1], [Part2], [Part3], [Part4], [Part5] FROM ( SELECT o.OrderDate, d.OrderID, d.PartID, d.Quantity FROM @MyOrderTable o LEFT JOIN @MyOrderDetailTable d ON o.orderid = d.orderid) p PIVOT (SUM(Quantity) FOR PartID IN ([Part1], [Part2], [Part3], [Part4], [Part5])) pvt ORDER BY Year(OrderDate), pvt.OrderID [1]: http://msdn.microsoft.com/en-gb/library/ms177410(v=sql.105).aspx --edit-- Given you have multiple Parts, and you can't guarantee which ones are in use during any particular period, you'll need a Dynamic Pivot, where we build up the ColumnList from the data that's there already, and use that to build up the SELECT statement that's then passed through to an EXEC: Use Tempdb GO CREATE TABLE #MyOrderTable (OrderID int IDENTITY(1,1), OrderDate Date) CREATE TABLE #MyOrderDetailTable (OrderID int, PartID char(5), Quantity int) INSERT INTO #MyOrderTable (OrderDate) VALUES ('2013-01-01'), ('2013-01-02'), ('2013-01-05'), ('2013-01-10') INSERT INTO #MyOrderDetailTable VALUES (1, 'PART1', 10), (2, 'PART3', 5), (2, 'PART5', 2), (3, 'PART2', 50), (4, 'PART1', 25), (4, 'PART3', 2), (4, 'PART4', 5) DECLARE @PivotSQL nvarchar(max) DECLARE @ColumnList nvarchar(max) SELECT @ColumnList = COALESCE(@ColumnList + ',[' + PartID + ']', '[' + PartID + ']') FROM (SELECT DISTINCT PartID FROM #MyOrderDetailTable) collist ORDER BY PartID --SELECT @ColumnList SELECT @PivotSQL = 'SELECT Year(OrderDate) AS [Year], OrderID, ' + @ColumnList + ' FROM ( SELECT o.OrderDate, d.OrderID, d.PartID, d.Quantity FROM #MyOrderTable o LEFT JOIN #MyOrderDetailTable d ON o.orderid = d.orderid) p PIVOT (SUM(Quantity) FOR PartID IN (' + @ColumnList + ')) pvt ORDER BY Year(OrderDate), pvt.OrderID' -- SELECT @PivotSQL EXEC (@PivotSQL)