Convert columnar data to rows
Posted on 2012-03-16
Thank you for looking at my question,
I have a table that lists the output from manufacturing orders in the format:
Date, Order No, Order Qty, Processed Qty, Status
Where Destination could be 'GOOD', 'SCRAP', 'LOST' or 'QA'
For an order that results in all pieces having the same status the data would look like
16/03/2012, JOB001, 20, 20, GOOD (or SCRAP / LOST / QA as necessary)
Equally, you could get
16/03/2012, JOB002, 40, 20, GOOD
16/03/2012, JOB002, 40, 10, SCRAP
16/03/2012, JOB002, 40, 10, QA
I need to query this data and output one row per job no in the format
Date, Job No, Order Qty, Good Qty, Scrap Qty, Lost Qty, QA Qty
Ideally, where an order has all parts with the same status I would like all the other status quantities to be zero.
Any help you can offer will be greatly appreciated.