Help with parent/child query please

  • Hi everyone,

    Trying to get this data to the following format but I can't seem to group it properly. Any pointers would be appreciated. Thank you

    drop table if exists #tmp
    create table #tmp (uniqueid uniqueidentifier, id int, [level] int, parent_id int, order_category varchar(100))
    insert into #tmp
    select '33E630DF-F653-4564-979F-DD34748760B9' as uniqueid, 101 as id, 1 as [level], null as parent_id, 'category' as order_category union all
    select '33E630DF-F653-4564-979F-DD34748760B9' as uniqueid, 102 as id, 2 as [level], 101 as parent_id, 'sub category ABC' as order_category union all
    select '33E630DF-F653-4564-979F-DD34748760B9' as uniqueid, 103 as id, 3 as [level], 102 as parent_id, 'sub sub category DFG' as order_category union all
    select '33E630DF-F653-4564-979F-DD34748760B9' as uniqueid, 104 as id, 2 as [level], 101 as parent_id, 'sub category QWE' as order_category union all
    select '33E630DF-F653-4564-979F-DD34748760B9' as uniqueid, 105 as id, 3 as [level], 102 as parent_id, 'sub sub category RTY' as order_category union all

    select '132E41DF-A75E-4A13-B3A5-9E1C4952F23A' as uniqueid, 101 as id, 1 as [level], null as parent_id, 'category' as order_category union all
    select '132E41DF-A75E-4A13-B3A5-9E1C4952F23A' as uniqueid, 102 as id, 2 as [level], 101 as parent_id,'sub category ABC' as order_category union all
    select '132E41DF-A75E-4A13-B3A5-9E1C4952F23A' as uniqueid, 103 as id, 3 as [level], 102 as parent_id,'sub sub category DFG' as order_category union all
    select '132E41DF-A75E-4A13-B3A5-9E1C4952F23A' as uniqueid, 104 as id, 2 as [level], 101 as parent_id,'sub category QWE' as order_category union all
    select '132E41DF-A75E-4A13-B3A5-9E1C4952F23A' as uniqueid, 105 as id, 3 as [level], 102 as parent_id,'sub sub category RTY' as order_category

    select * from #tmp

    -- desired output (use id of highest level)

    select '33E630DF-F653-4564-979F-DD34748760B9' as uniqueid, 101 as id, 'category' as order_category_1, 'sub category ABC' as order_category_2, 'sub sub category DFG' as order_category_3 union all
    select '33E630DF-F653-4564-979F-DD34748760B9' as uniqueid, 101 as id, 'category' as order_category_1, 'sub category QWE' as order_category_2, 'sub sub category RTY' as order_category_3 union all

    select '132E41DF-A75E-4A13-B3A5-9E1C4952F23A' as uniqueid, 101 as id, 'category' as order_category_1, 'sub category ABC' as order_category_2, 'sub sub category DFG' as order_category_3 union all
    select '132E41DF-A75E-4A13-B3A5-9E1C4952F23A' as uniqueid, 101 as id, 'category' as order_category_1, 'sub category QWE' as order_category_2, 'sub sub category RTY' as order_category_3

     

     

    __________________________
    Allzu viel ist ungesund...

  • Based on your provided sample data, this query will produce the correct result.

    WITH cteBase AS (
    SELECT t0.*, rn = ROW_NUMBER() OVER (PARTITION BY t0.uniqueid, t0.[level] ORDER BY t0.id)
    FROM #tmp AS t0
    )
    SELECT t1.uniqueid, t1.id
    , order_category_1 = t1.order_category
    , order_category_2 = t2.order_category
    , order_category_3 = t3.order_category
    FROM cteBase AS t1
    CROSS APPLY (SELECT s2.order_category, s2.rn
    FROM cteBase AS s2
    WHERE s2.uniqueid = t1.uniqueid
    AND s2.parent_id = t1.id
    AND s2.[level] = 2
    ) AS t2
    OUTER APPLY (SELECT s3.order_category
    FROM cteBase AS s3
    WHERE s3.uniqueid = t1.uniqueid
    AND s3.rn = t2.rn
    AND s3.[level] = 3
    ) AS t3
    WHERE t1.[level] = 1;

    • This reply was modified 4 weeks ago by  DesNorton.
  • The only relational operator which seems required is a partial CROSS JOIN of #tmp (WHERE 'parent_id' IS NULL) to #tmp (WHERE 'parent_id' IS NOT NULL) ON 'uniqueid'.  Then it's a crosstab query using conditional aggregation.  This assumes the 'id' column is a uniform, ordered sequence partitioned by uniqueid

    with
    grp_cte as (
    select t.*,
    tt.[level] as plus_level,
    tt.order_category as plus_order_category,
    tt.id as plus_id,
    tt.id-tt.[level] grp
    from #tmp t
    join #tmp tt on t.uniqueid=tt.uniqueid
    and tt.parent_id is not null
    where t.parent_id is null)
    select uniqueid, id, order_category order_category_1,
    max(iif(plus_level=2, plus_order_category, null)) order_category_2,
    max(iif(plus_level=3, plus_order_category, null)) order_category_3
    from grp_cte
    group by uniqueid, id, order_category, grp
    order by uniqueid desc, grp;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • This is a parent/child list.  There's a whole lot more that can be done with it if it's recognized for what it can do including but not limited to "Nest Sets".

    Are ya sure that all you're ever  going to need to do is list some names in the correct order in a max of 3 columns?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for the feedback. I've been trying to apply the above to my data set but somehow it won't roll up properly when I do the grouping, I didn't do a very good job with my sample data either thinking I could figure this out myself. Below is closer to what I want (the numbers are made up, please disregard). Appreciate the help and patience.

    Edit: for now, there would only be 3 categories (at level 2-3-4)

    Desired result:

    Screenshot 2025-01-16 201222

    Test data:

    CREATE TABLE #tmp(
    [uniqueid] [varchar](100) NULL,
    [id] [int] NULL,
    [level] [int] NULL,
    [parent_id] [int] NULL,
    [attribute] [varchar](100) NULL,
    [value] [varchar](100) NULL
    ) ON [PRIMARY]
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 130, 2, 129, N'Category', N'Drinks')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 130, 2, 129, N'QtyLeft', N'28')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 130, 2, 129, N'AmtSold', N'1959')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 130, 2, 129, N'QtySold', N'152')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 130, 2, 129, N'AmtRefund', N'4.8')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 130, 2, 129, N'AmtDiscount', N'2.86')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 131, 3, 130, N'Category', N'Soft Drinks')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 131, 3, 130, N'QtyLeft', N'112')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 131, 3, 130, N'AmtSold', N'5877')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 131, 3, 130, N'QtySold', N'380')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 131, 3, 130, N'AmtRefund', N'7.2')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 131, 3, 130, N'AmtDiscount', N'4.29')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 134, 4, 131, N'Category', N'Fuzzy Soft Drinks')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 134, 4, 131, N'QtyLeft', N'196')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 134, 4, 131, N'AmtSold', N'9795')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 134, 4, 131, N'QtySold', N'608')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 134, 4, 131, N'AmtRefund', N'9.6')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 134, 4, 131, N'AmtDiscount', N'5.72')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 135, 3, 130, N'Category', N'Alcoholic Drinks')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 135, 3, 130, N'QtyLeft', N'280')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 135, 3, 130, N'AmtSold', N'13713')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 135, 3, 130, N'QtySold', N'836')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 135, 3, 130, N'AmtRefund', N'7.2')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 135, 3, 130, N'AmtDiscount', N'4.29')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 137, 4, 135, N'Category', N'Spirits')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 137, 4, 135, N'QtyLeft', N'364')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 137, 4, 135, N'AmtSold', N'17631')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 137, 4, 135, N'QtySold', N'1064')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 137, 4, 135, N'AmtRefund', N'9.6')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'33E630DF-F653-4564-979F-DD34748760B9', 137, 4, 135, N'AmtDiscount', N'5.72')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 130, 2, 129, N'Category', N'Drinks')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 130, 2, 129, N'QtyLeft', N'448')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 130, 2, 129, N'AmtSold', N'21549')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 130, 2, 129, N'QtySold', N'1292')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 130, 2, 129, N'AmtRefund', N'4.8')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 130, 2, 129, N'AmtDiscount', N'2.86')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 131, 3, 130, N'Category', N'Soft Drinks')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 131, 3, 130, N'QtyLeft', N'532')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 131, 3, 130, N'AmtSold', N'25467')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 131, 3, 130, N'QtySold', N'1520')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 131, 3, 130, N'AmtRefund', N'7.2')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 131, 3, 130, N'AmtDiscount', N'4.29')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 134, 4, 131, N'Category', N'Fuzzy Soft Drinks')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 134, 4, 131, N'QtyLeft', N'616')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 134, 4, 131, N'AmtSold', N'29385')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 134, 4, 131, N'QtySold', N'1748')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 134, 4, 131, N'AmtRefund', N'9.6')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 134, 4, 131, N'AmtDiscount', N'5.72')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 135, 3, 130, N'Category', N'Alcoholic Drinks')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 135, 3, 130, N'QtyLeft', N'700')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 135, 3, 130, N'AmtSold', N'33303')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 135, 3, 130, N'QtySold', N'1976')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 135, 3, 130, N'AmtRefund', N'7.2')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 135, 3, 130, N'AmtDiscount', N'4.29')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 137, 4, 135, N'Category', N'Beer')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 137, 4, 135, N'QtyLeft', N'784')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 137, 4, 135, N'AmtSold', N'37221')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 137, 4, 135, N'QtySold', N'2204')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 137, 4, 135, N'AmtRefund', N'9.6')
    GO
    INSERT #tmp ([uniqueid], [id], [level], [parent_id], [attribute], [value]) VALUES (N'132E41DF-A75E-4A13-B3A5-9E1C4952F23A', 137, 4, 135, N'AmtDiscount', N'5.72')
    GO

    • This reply was modified 3 weeks, 6 days ago by  Mr. Holio.

    __________________________
    Allzu viel ist ungesund...

  • Based on your provided sample data, this query will produce the correct result.

    WITH cteCrossTab AS (
    /* Use a Cross-Tab query to get the data in a tabular format */
    SELECT t.[uniqueid], t.[id], t.[parent_id], t.[level]
    , Order_Category_1 = MAX(CASE WHEN t.[attribute] = 'Category' AND t.[level] = 2 THEN t.[value] END)
    , Order_Category_2 = MAX(CASE WHEN t.[attribute] = 'Category' AND t.[level] = 3 THEN t.[value] END)
    , Order_Category_3 = MAX(CASE WHEN t.[attribute] = 'Category' AND t.[level] = 4 THEN t.[value] END)
    , QtyLeft = MAX(CASE WHEN t.[attribute] = 'QtyLeft' THEN t.[value] END)
    , AmtSold = MAX(CASE WHEN t.[attribute] = 'AmtSold' THEN t.[value] END)
    , QtySold = MAX(CASE WHEN t.[attribute] = 'QtySold' THEN t.[value] END)
    , AmtRefund = MAX(CASE WHEN t.[attribute] = 'AmtRefund' THEN t.[value] END)
    , AmtDiscount = MAX(CASE WHEN t.[attribute] = 'AmtDiscount' THEN t.[value] END)
    FROM #tmp AS t
    GROUP BY t.[uniqueid], t.[id], t.[parent_id], t.[level]
    )
    /* Fill in the missing Order_Category labels */
    SELECT ct.[uniqueid]/*, ct.[id], ct.[parent_id], ct.[level]*/
    , Order_Category_1 = FIRST_VALUE(ct.Order_Category_1) OVER (PARTITION BY ct.[uniqueid] ORDER BY ct.[id])
    , Order_Category_2 = COALESCE(ct.Order_Category_2, LAG(ct.Order_Category_2) OVER (PARTITION BY ct.[uniqueid] ORDER BY ct.[id]), '')
    , Order_Category_3 = COALESCE(ct.Order_Category_3, '')
    , ct.QtyLeft
    , ct.AmtSold
    , ct.QtySold
    , ct.AmtRefund
    , ct.AmtDiscount
    FROM cteCrossTab AS ct
    ORDER BY ct.[uniqueid] DESC, ct.[id];
  • Thank you, very close but ideally all categories should have a parent (or two) so if there's a category3, then category1 and category2 should not be null, etc. I managed to get this work with several left joins, not pretty but works - probably not a good approach if you want to go deeper than level 3. -Many thanks for the feedback again!!

    __________________________
    Allzu viel ist ungesund...

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

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