x

need help creating a part/order matrix w/qty for each part

link textI need to provide a part matrix showing all parts as columns and the rows need to be orders that sold those parts and under each part should be the total quantity for that order. This needs to be separted by year for the last three years. I'm a novice at sql server queries and don't know how to get this format.

Not sure how to format example below. Orders and part1 - part5 as columns, Order1 - order 4 as rows. numbers as qty under part columns.

 > YEAR
 ORDERS   PART1   PART2   PART3   PART4  PART5
 order1     10         
 order2                     5              2
 order3             50
 order4     25              2        5

  


more ▼

asked Jan 24, 2013 at 06:04 PM in Default

avatar image

smithj338
90 2 3 6

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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` 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

--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)
 
more ▼

answered Jan 25, 2013 at 02:46 PM

avatar image

ThomasRushton ♦♦
41.5k 20 50 53

Thanks for the help Thomas. Nice job. I'm not sure how to code for several to several thousand parts. The feed for this will be selecting parts from orders over a period of time so the list of parts in the select would have to be dynamic.

Jan 25, 2013 at 04:09 PM smithj338

OK, so we're looking for a dynamic pivot. Hang on, I'll try to lash one up...

Jan 25, 2013 at 04:31 PM ThomasRushton ♦♦

Thanks again for the quick help and great coding techniques. I'll have to try to insert my data into this. It's going to take me a while to digest this as your skills are way above mine. I'm not sure how to mark this question as answered.

Jan 25, 2013 at 09:52 PM smithj338

No problem - it's what we're here for. If you want an explanation for how any of that works, just shout. Someone will answer.

You see the "thumbs up" icon just above the zero next to the answer? Click that, and there's a tick just below the thumbs down. Click that.

Jan 25, 2013 at 10:14 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x417

asked: Jan 24, 2013 at 06:04 PM

Seen: 654 times

Last Updated: Jan 28, 2013 at 01:34 AM

Copyright 2016 Redgate Software. Privacy Policy