Complex SQL Pivot Query/View

Posted on 2009-02-19
Last Modified: 2013-11-15
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.
Question by:Vyskol
    LVL 40

    Expert Comment

    What is your database?

    Author Comment

    I'm using 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"
    SELECT 'Build' AS "Label", "BuildDate" AS "Date", "PartID" AS "Part", "BuildQtyAdjusted" AS "Qty" FROM "Build" AS "Build"
    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.
    LVL 40

    Accepted Solution


    -- 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"
    SELECT 'Build' AS "Label", "BuildDate" AS "Date", "PartID" AS "Part", "BuildQtyAdjusted" AS "Qty" FROM "Build" AS "Build"
    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


    Author Comment

    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!
    LVL 40

    Expert Comment

    I am not familiar with 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.

    Author Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Read about achieving the basic levels of HRIS security in the workplace.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now