• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 846
  • Last Modified:

Complex SQL Pivot Query/View

Sample Data obtained through a union query:

Label  |  Date             |  Part  |  Qty
------------------------------------------
Build   |  2009/01/16  |  1      |  233
Build   |  2009/01/16  |  2      |  343
Build   |  2009/02/17  |  1      |  233
Count  |  2009/01/12 |  1      |  153
Count  |  2009/01/12 |  2      |  146
PO      |  2009/02/03  |  1      |  100
PO      |  2009/02/04  |  2      |  150


Desired Output:

Part  |  2009/01/12 Count  |  2009/01/16 Build  |  2009/02/03 PO  |  2009/02/04 PO  |  ....
-------------------------------------------------------------------------------------------------------
1      |          153                |          233               |          100            |                            | ...
2      |          146                |          343               |                            |            150          | ...


Hmm, also need an add/short column, probably after each Build, that is basically Sum(Count) + Sum(PO) - Sum(Build), up to that point.
0
Vyskol
Asked:
Vyskol
  • 3
  • 3
1 Solution
 
SharathData EngineerCommented:
What is your database?
0
 
VyskolAuthor Commented:
I'm using OpenOffice.org Base. Though I'm having 2nd thoughts on that choice. But I'm confident I could translate any solution to whatever platform I end up with. So long as it's not too specific to a proprietary database type.

I've actually made some decent headway on this problem, and once I started breaking it down, it's really not as complicated as I originally thought. My main obstacle at the moment is either OO.o's PIVOT syntax, or my mistaken assumption that it supports PIVOT at all.

Thus far I have:

SELECT Part, CONCAT (CONCAT(TO_CHAR("Date", 'YYYY/MM/DD'), ' '), "Label") AS "LabelDate", "Qty" FROM (
SELECT 'Count' AS "Label", "PartCountDateTime" AS "Date", "PartID" AS "Part", "PartCountQty" AS "Qty" FROM "PartCount" AS "PartCount"
UNION
SELECT 'Build' AS "Label", "BuildDate" AS "Date", "PartID" AS "Part", "BuildQtyAdjusted" AS "Qty" FROM "Build" AS "Build"
UNION
SELECT 'Purchase Order' AS "Label", "PODate" AS "Date", "PartID" as "Part", "POQty" AS "Qty" FROM "PurchaseOrder" AS "PurchaseOrder")

Giving me:

Part  |  LabelDate              |  Qty
----------------------------------------
 1     |  2009/02/23 Build    |  236
 2     |  2009/02/25 Build    |  245
 1     |  2009/02/17 Count  |  1234
 2     |  2009/02/17 Count  |  3432
 1     |  2009/02/25 PO       |  234
 2     |  2009/02/25 PO       |  343
 2     |  2009/02/26 PO       |  34

(pardon me for not matching up the test data in the original question)

Leaving me with the PIVOT and the Over/Short Column.
0
 
SharathData EngineerCommented:

-- I have implemented this using SQL Server 2005 or more. 
-- Store the result of your UNION query into a temp table
SELECT Part, CONCAT (CONCAT(TO_CHAR("Date", 'YYYY/MM/DD'), ' '), "Label") AS "LabelDate", "Qty" 
  INTO TempTable
  FROM (
SELECT 'Count' AS "Label", "PartCountDateTime" AS "Date", "PartID" AS "Part", "PartCountQty" AS "Qty" FROM "PartCount" AS "PartCount"
UNION
SELECT 'Build' AS "Label", "BuildDate" AS "Date", "PartID" AS "Part", "BuildQtyAdjusted" AS "Qty" FROM "Build" AS "Build"
UNION
SELECT 'Purchase Order' AS "Label", "PODate" AS "Date", "PartID" as "Part", "POQty" AS "Qty" FROM "PurchaseOrder" AS "PurchaseOrder")
 
-- you can try like this
select * 
  from (select * from TempTable) t2
 pivot (sum(Qty) for LabelDate in ([2009/02/23 Build],[2009/02/25 Build],[2009/02/17 Count],[2009/02/25 PO],[2009/02/26 PO]))p 
 
/*
Query result
Part	2009/02/23 Build	2009/02/25 Build	2009/02/17 Count	2009/02/25 PO	2009/02/26 PO
1	236	NULL	1234	234	NULL
2	NULL	245	3432	343	34
*/
-- if you don't know how many LabelDates, you have in your TempTable, then use the dynamic sql.
declare @sql varchar(8000)
set @sql = (select distinct ',['+LabelDate+']' from TempTable for xml path(''))
set @sql = '
SELECT Part' + @sql  + ' 
FROM  (SELECT Part,LabelDate,Qty FROM TempTable) t2
PIVOT (SUM(Qty) FOR LabelDate IN ('+substring(@sql,2,8000)  +')) AS p'
exec (@sql)
/*
Query result
Part	2009/02/17 Count	2009/02/23 Build	2009/02/25 Build	2009/02/25 PO	2009/02/26 PO
1	1234	236	NULL	234	NULL
2	3432	NULL	245	343	34
*/
 
-- drop the temp table
drop table TempTable

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
VyskolAuthor Commented:
I'm having troubles getting this running in OO.o. Not sure about its scripting support, but I can see that your solution will work.

Any ideas for the Over/Short column?
Needs to go back to the most recent Count entry and then tally up the POs (add) and Builds (subtract) up to the specified (current) date.

Thanks for the help!
0
 
SharathData EngineerCommented:
I am not familiar with OpenOffice.org. I can try converting this code to support your application but I cannot guarantee on this to you.
 I am loaded with much work today.
0
 
VyskolAuthor Commented:
No, that won't be necessary, but thank you very much. :)
It turns out that OO.o doesn't support crosstab/pivot queries, so the question over the rest of the script is a moot point.

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now