Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Self-Join with aggregation and pivot ? RE: Self-Join with aggregation and pivot ?

  • February 7, 2018 at 1:45 pm

    #1978953

    will_william - Wednesday, February 7, 2018 1:04 PM

    Hello -
    I need to generate a result set that will be coming from a table that includes parent and child rows. The result needs to be one row per parent and include the child row information (some aggregation like min for a date, sum of amounts). And, also include rows for the child data.

    The table contains sales data and there are times when an order is changed and generates some related orders. The need is to have the original parent order on one row, that includes any information about the child orders and also one to N rows for the child orders with their details.

    I have tried to use a CTE to get the self join, but i keep getting a cartesian product. I am wondering if i will need to build out separate temp tables for the query...one for the parent rows, one for the child rows?

    Here is some t-sql to generate some sample data:
    /** setting up table and inserting data **/
    declare @mytable table (Status_Code varchar(100), Region varchar(100), Order_Amount float, OrderNum varchar(100), Orig_OrderNum varchar(100), OrderComplete datetime, OrderCreate datetime )

    Insert into @mytable
    Select 'Complete', 'Southeast', 15000.00, '55544400', '55544400', '2016-11-11', '2016-11-11'
    Union All
    Select 'Change_Complete', 'Southeast', 2500.00, '5556700', '55544400', '2016-12-09', '2016-12-09'
    Union All
    Select 'Change_Complete', 'Southeast', 1000.00, '5558890', '55544400', '2016-12-20', '2016-12-20'
    Union All
    Select 'Change_Complete', 'Southeast', 4400.00, '5567700', '55544400', '2017-01-14', '2017-01-14'

    Select * from @mytable

    The results need to look like this;

    OrderNum    Order_Complete    Order_Amount    Child Order Date (min)    Child Order Amount    Child_order_Date_new    Child_Order_Amt_New    Parent_Order_Num
    55544400    2016-11-11             15000                    2016-12-09                         7900                               null                                null                                          null
    5556700                null                    null                       null                                       null                           2016-12-09                       2500                                55544400
    5558890                null                    null                       null                                       null                            2016-12-20                      1000                                55544400
    5567700                null                    null                       null                                        null                           2017-01-14                       4400                               55544400

    Thanks for any suggestions or advice.
     - will

    You don't need to self join or pivot.

    SELECT m.OrderNum,
       CASE WHEN m.OrderNum = m.Orig_OrderNum THEN m.OrderComplete END AS Order_Complete,
       CASE WHEN m.OrderNum = m.Orig_OrderNum THEN m.Order_Amount END AS Order_Amount,
       CASE WHEN m.OrderNum = m.Orig_OrderNum THEN MIN( CASE WHEN m.OrderNum != m.Orig_OrderNum THEN m.OrderCreate END) OVER(PARTITION BY m.Orig_OrderNum) END AS Child_Order_Date,
       CASE WHEN m.OrderNum = m.Orig_OrderNum THEN SUM( CASE WHEN m.OrderNum != m.Orig_OrderNum THEN m.Order_Amount END) OVER(PARTITION BY m.Orig_OrderNum) END AS Child_Order_Date,
       CASE WHEN m.OrderNum != m.Orig_OrderNum THEN m.OrderCreate END AS Child_order_Date_new,
       CASE WHEN m.OrderNum != m.Orig_OrderNum THEN m.Order_Amount END AS Child_Order_Amt_New,
       NULLIF( m.Orig_OrderNum, m.OrderNum) AS Parent_Order_Num
    FROM @mytable AS m;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2