How to flatten results from parent/children tables in view

  • I have a parent table and 2 children tables that store date ranges. I'm trying to create a view to support a transaction report and I'm having trouble getting the rows flattened so I don't have to have so many rows returned.

    CREATE TABLE Parent (

    PntKeyint,

    PntQtyint)

    CREATE TABLE Child1 (

    Cd1Keyint,

    Cd1PntKeyint,

    Cd1FromDatedate,

    Cd1ToDatedate)

    CREATE TABLE Child2 (

    Cd2Keyint,

    Cd2PntKeyint,

    Cd2FromDatedate,

    Cd2ToDatedate)

    INSERT INTO Parent (PntKey, PntQty)

    SELECT1, 100

    UNION ALL SELECT 2, 200

    UNION ALL SELECT 3, 300

    INSERT INTO Child1 (Cd1Key, Cd1PntKey, Cd1FromDate, Cd1ToDate)

    SELECT 1,1,'1-1-2012','2-1-2012'

    UNION ALL SELECT 2,1,'4-1-2012','5-1-2012'

    UNION ALL SELECT 3,2,'1-15-2012','5-15-2012'

    UNION ALL SELECT 4,3,'5-1-2012','7-1-2012'

    INSERT INTO Child2 (Cd2Key, Cd2PntKey, Cd2FromDate, Cd2ToDate)

    SELECT 1,1,'12-1-2011','12-31-2011'

    UNION ALL SELECT 2,2,'1-1-2012','2-1-2012'

    UNION ALL SELECT 3,2,'2-15-2012','5-15-2012'

    UNION ALL SELECT 4,3,'9-1-2012','10-1-2012'

    SELECTPntKey,

    Qty,

    FromDate1,

    ToDate1,

    FromDate2,

    ToDate2

    FROM (

    SELECTPntKeyPntKey,

    PntQtyQty,

    NULLFromDate1,

    NULLToDate1,

    NULLFromDate2,

    NULLToDate2

    FROMParent

    UNION ALL

    SELECTCd1PntKeyPntKey,

    NULLQty,

    Cd1FromDateFromDate1,

    Cd1ToDateToDate1,

    NULLFromDate2,

    NULLToDate2

    FROMChild1

    UNION ALL

    SELECTCd2PntKeyPntKey,

    NULLQty,

    NULLFromDate1,

    NULLToDate1,

    Cd2FromDateFromDate2,

    Cd2ToDateToDate2

    FROMChild2

    ) AS WORK

    ORDER BY PntKey, Qty desc

    The final SELECT statement returns results like this:

    PntKeyQtyFromDate1ToDate1 FromDate2ToDate2

    1100NULL NULL NULL NULL

    1NULL2012-01-012012-02-01NULL NULL

    1NULL2012-04-012012-05-01NULL NULL

    1NULLNULL NULL 2011-12-012011-12-31

    2200NULL NULL NULL NULL

    2NULLNULL NULL 2012-01-012012-02-01

    2NULLNULL NULL 2012-02-152012-05-15

    2NULL2012-01-152012-05-15NULL NULL

    3300NULL NULL NULL NULL

    3NULL2012-05-012012-07-01NULL NULL

    3NULLNULL NULL 2012-09-012012-10-01

    But what I'd really like is something like this:

    PntKeyQtyFromDate1ToDate1 FromDate2ToDate2

    11001/1/2012 2/1/2012 12/1/201112/31/2011

    1NULL4/1/2012 5/1/2012 NULL NULL

    22001/15/20125/15/20121/1/20122/1/2012

    2NULLNULL NULL 2/15/20125/15/2012

    33005/1/2012 7/1/2012 9/1/2012 10/1/2012

    Is it possible to get the data back in a flatter way?

    Thanks.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Thanks for responding. I actually did try using joins as my first choice, but the results were incorrect:

    PntKey Qty FromDate1 ToDate1 FromDate2 ToDate2

    ----------- ----------- ---------- ---------- ---------- ----------

    1 100 2012-01-01 2012-02-01 2011-12-01 2011-12-31

    1 100 2012-04-01 2012-05-01 2011-12-01 2011-12-31

    2 200 2012-01-15 2012-05-15 2012-01-01 2012-02-01

    2 200 2012-01-15 2012-05-15 2012-02-15 2012-05-15

    3 300 2012-05-01 2012-07-01 2012-09-01 2012-10-01

    (5 row(s) affected)

    For PntKey 1 the FromDate2 and ToDate2 are duplicated and for PntKey 2 the FromDate1 and ToDate1 are duplicated, but I only want them to appear once. Also, the Qty gets duplicated, which will overstate it. It seems like it shouldn't be this much of a challenge but I've been struggling to come up with something that works.

  • I came up with this code:

    selectPntKey,

    RowNumber,

    CASE

    WHEN RowNumber = 1 THEN Qty

    ELSE 0

    ENDQty,

    min(FromDate1),

    max(ToDate1),

    min(FromDate2),

    max(ToDate2)

    from

    (

    selectPntKey,

    ROW_NUMBER() OVER(PARTITION BY PntKey ORDER BY PntKey) AS 'RowNumber',

    Parent.PntQty Qty,

    Child1.Cd1FromDate FromDate1,

    Child1.Cd1ToDate ToDate1,

    null FromDate2,

    null ToDate2

    FROM Parent

    LEFT JOIN Child1 ON Child1.Cd1PntKey = Parent.PntKey

    union

    select PntKey,

    ROW_NUMBER() OVER(PARTITION BY PntKey ORDER BY PntKey) AS 'RowNumber',

    Parent.PntQty Qty,

    null FromDate1,

    null ToDate1,

    Child2.Cd2FromDate FromDate2,

    Child2.Cd2ToDate ToDate2

    FROM Parent

    LEFT JOIN Child2 ON Child2.Cd2PntKey = Parent.PntKey

    ) as work

    group by PntKey,

    RowNumber,

    CASE

    WHEN RowNumber = 1 THEN Qty

    ELSE 0

    END

    And I get results like this:

    PntKey RowNumber Qty FromDate1 ToDate1 FromDate2 ToDate2

    ----------- -------------------- ----------- ---------- ---------- ---------- ----------

    1 1 100 2012-01-01 2012-02-01 2011-12-01 2011-12-31

    1 2 0 2012-04-01 2012-05-01 NULL NULL

    2 1 200 2012-01-15 2012-05-15 2012-01-01 2012-02-01

    2 2 0 NULL NULL 2012-02-15 2012-05-15

    3 1 300 2012-05-01 2012-07-01 2012-09-01 2012-10-01

    Warning: Null value is eliminated by an aggregate or other SET operation.

    (5 row(s) affected)

  • You can try this. It looks like the results you're after but why you'd want such bizarre results eludes me (enlightnment requested :-)).

    SELECT PntKey=ISNULL(PntKey, Cd2PntKey)

    ,PntQty=CASE WHEN n1 = 1 THEN PntQty END

    ,FromDate1, ToDate1

    ,FromDate2=Cd2FromDate, ToDate2=Cd2ToDate

    FROM (

    SELECT p.PntKey

    ,p.PntQty

    ,FromDate1=Cd1FromDate

    ,ToDate1=Cd1ToDate

    ,n1=ROW_NUMBER() OVER (PARTITION BY PntKey ORDER BY Cd1Key)

    FROM #Parent p

    RIGHT JOIN #Child1 c1 ON p.PntKey = c1.Cd1PntKey) a

    FULL JOIN (

    SELECT Cd2Key, Cd2PntKey, Cd2FromDate, Cd2ToDate

    ,n2=ROW_NUMBER() OVER (PARTITION BY Cd2PntKey ORDER BY Cd2Key)

    FROM #Child2) c2

    ON a.PntKey = c2.Cd2PntKey AND a.n1 = c2.n2

    ORDER BY PntKey


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply