January 15, 2025 at 11:28 pm
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...
January 16, 2025 at 9:44 am
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;
January 16, 2025 at 2:21 pm
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
January 16, 2025 at 9:32 pm
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
Change is inevitable... Change for the better is not.
January 17, 2025 at 4:15 am
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:
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
__________________________
Allzu viel ist ungesund...
January 17, 2025 at 9:57 am
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];
January 21, 2025 at 3:24 am
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