Hi,
I am stuck with a problem & was hoping if someone can please help me. Not sure if this is a sub-query or would I need a CTE. I have 3 tables for reference and not sure how can I get desired output (screenshot attached). I tried but, couldn't get my head around. Thanks in advance.
-- Table #1
if exists
(select * from dbo.sysobjects where id = object_id(N'[Product_Main]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Product_Main]
CREATE TABLE Product_Main (
product_id int,
barcode int,
product_name varchar(255)
);
INSERT INTO Product_Main SELECT 125218,9452132,'Product A'
INSERT INTO Product_Main SELECT 125224,9452185,'Product B'
INSERT INTO Product_Main SELECT 125225,9452187,'Product C'
INSERT INTO Product_Main SELECT 127133,9452149,'Product D'
INSERT INTO Product_Main SELECT 127334,9452144,'Product E'
INSERT INTO Product_Main SELECT 127484,9452879,'Product F'
INSERT INTO Product_Main SELECT 127883,9452245,'Product G'
INSERT INTO Product_Main SELECT 127456,9452199,'Product H'
INSERT INTO Product_Main SELECT 925225,5452187,'Product I'
INSERT INTO Product_Main SELECT 827133,9782149,'Product J'
INSERT INTO Product_Main SELECT 727334,1052144,'Product K'
INSERT INTO Product_Main SELECT 627484,9112879,'Product L'
INSERT INTO Product_Main SELECT 427883,9262245,'Product M'
INSERT INTO Product_Main SELECT 327456,9452299,'Product N'
GO;
SELECT * FROM Product_Main;
-- Table #2
if exists
(select * from dbo.sysobjects where id = object_id(N'[Product_Helper]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Product_Helper]
CREATE TABLE Product_Helper (
product_id int,
promo_id int,
promogroup varchar(255)
);
INSERT INTO Product_Helper SELECT 125218,5978,'Promo 1'
INSERT INTO Product_Helper SELECT 125224,5978,'Promo 1'
INSERT INTO Product_Helper SELECT 125225,5978,'Promo 1'
INSERT INTO Product_Helper SELECT 127133,5979,'Promo 2'
INSERT INTO Product_Helper SELECT 127334,5979,'Promo 2'
INSERT INTO Product_Helper SELECT 127484,5979,'Promo 2'
INSERT INTO Product_Helper SELECT 127883,5980,'Promo 3'
INSERT INTO Product_Helper SELECT 127456,5980,'Promo 3'
INSERT INTO Product_Helper SELECT 925225,6666,'Promo 4'
INSERT INTO Product_Helper SELECT 827133,6666,'Promo 4'
INSERT INTO Product_Helper SELECT 727334,6666,'Promo 4'
INSERT INTO Product_Helper SELECT 627484,6667,'Promo 5'
INSERT INTO Product_Helper SELECT 427883,6667,'Promo 5'
INSERT INTO Product_Helper SELECT 327456,7001,'Promo 6'
GO;
SELECT * FROM Product_Helper
-- Table #3
if exists
(select * from dbo.sysobjects where id = object_id(N'[Display_Product_Allocation]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Display_Product_Allocation]
CREATE TABLE Display_Product_Allocation (
display_plan_id int,
promo_id int
);
INSERT INTO Display_Product_Allocation SELECT 30275,5978
INSERT INTO Display_Product_Allocation SELECT 30275,5978
INSERT INTO Display_Product_Allocation SELECT 30275,5978
INSERT INTO Display_Product_Allocation SELECT 30275,5979
INSERT INTO Display_Product_Allocation SELECT 30275,5979
INSERT INTO Display_Product_Allocation SELECT 30275,5979
INSERT INTO Display_Product_Allocation SELECT 30275,5980
INSERT INTO Display_Product_Allocation SELECT 30275,5980
INSERT INTO Display_Product_Allocation SELECT 40275,6666
INSERT INTO Display_Product_Allocation SELECT 40275,6667
INSERT INTO Display_Product_Allocation SELECT 40275,6668
INSERT INTO Display_Product_Allocation SELECT 41275,7000
INSERT INTO Display_Product_Allocation SELECT 41275,7001
GO;
--SELECT * FROM Display_Product_Allocation
/*
(SELECT Helper.promo_id, STRING_AGG(Main.product_id,';') AS Concat_Product_Id, STRING_AGG(Main.barcode,';') AS Concat_barcode ,STRING_AGG(Main.product_name,';') AS Concat_Product_name
FROM Product_Main Main
LEFT JOIN Product_Helper Helper on main.product_id = Helper.product_id
GROUP BY Helper.promo_id) AS FocusTable
SELECT DISTINCT allocation.display_plan_id
FROM Display_Product_Allocation allocation
LEFT JOIN FocusTable ON allocation.promo_id = focustable.promo_id
GROUP BY allocation.display_plan_id
*/
June 4, 2021 at 9:24 am
Seems like all you need is a few STRING_AGGfunctions:
SELECT DPA.display_plan_id,
STRING_AGG(PH.promo_id,';') WITHIN GROUP (ORDER BY PH.promo_id) AS Promo_id,
STRING_AGG(PM.barcode,';') WITHIN GROUP (ORDER BY PH.promo_id) AS barcode,
STRING_AGG(PM.product_name,';') WITHIN GROUP (ORDER BY PH.promo_id) AS Product_name,
STRING_AGG(PH.promogroup,';') WITHIN GROUP (ORDER BY PH.promo_id) AS promogroup
FROM dbo.Display_Product_Allocation DPA
JOIN dbo.Product_Helper PH ON DPA.promo_id = PH.promo_id
JOIN dbo.Product_Main PM ON PH.product_id = PM.product_id
GROUP BY DPA.display_plan_id;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Not Sure whether its right approach or not. But got required results with below query
;WITH promoData(display_plan_id,promo_id,product_id,promogroup,barcode,product_name)
AS
(
SELECT DPA.display_plan_id,
DPA.promo_id,
PH.product_id,
PH.promogroup,
PM.barcode,
PM.product_name
FROM dbo.Display_Product_Allocation DPA
LEFT JOIN dbo.Product_Helper PH ON DPA.promo_id = PH.promo_id
LEFT JOIN dbo.Product_Main PM ON PH.product_id = PM.product_id
)
SELECT P.display_plan_id,
P.promo_id,
PRID.product_id,
B.barcode,
PN.product_name,
PG.promogroup
FROM
(
SELECT display_plan_id,
STRING_AGG(promo_id,';') AS promo_id
FROM
(
SELECT DISTINCT display_plan_id,promo_id
FROM promoData
) T
GROUP BY display_plan_id
) P
JOIN
(
SELECT display_plan_id,
STRING_AGG(product_id,';') AS product_id
FROM
(
SELECT DISTINCT display_plan_id,product_id
FROM promoData
) T
GROUP BY display_plan_id
) PRID ON PRID.display_plan_id = P.display_plan_id
JOIN
(
SELECT display_plan_id,
STRING_AGG(promogroup,';') AS promogroup
FROM
(
SELECT DISTINCT display_plan_id,promogroup
FROM promoData
) T
GROUP BY display_plan_id
) PG ON PG.display_plan_id = P.display_plan_id
JOIN
(
SELECT display_plan_id,
STRING_AGG(barcode,';') AS barcode
FROM
(
SELECT DISTINCT display_plan_id,barcode
FROM promoData
) T
GROUP BY display_plan_id
) B ON B.display_plan_id = P.display_plan_id
JOIN
(
SELECT display_plan_id,
STRING_AGG(product_name,';') AS product_name
FROM
(
SELECT DISTINCT display_plan_id,product_name
FROM promoData
) T
GROUP BY display_plan_id
) PN ON PN.display_plan_id = P.display_plan_id
June 20, 2021 at 11:27 pm
Thanks - that's perfect!
June 21, 2021 at 4:28 am
Thanks - that's perfect!
I don't have the time to try it out tonight even with the great test data posted but look at the STRING_AGG function again. It does have a "WITHIN GROUP" feature that might turn this into a cake walk.
STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2021 at 1:34 pm
Thanks - that's perfect!
The reason I made the suggestion above is because the code that you think is perfect is doing 10 table scans on your test data. Your test data is quite small and so the code appears to be snappy but ask yourself what it's going to do both for performance and resource usage when it gets to prod???
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2021 at 11:33 pm
Hiya Jeff,
Apologies for the delayed response. I absolutely agree with you however, max rows that will return in prod. is only 1200. So, i think I am able to get away this time as this is only a sort of look up table.
Surely, if I had transactional data coming into this table then def. I am screwed and would need to find a better solution. Just out of curiosity what would you do differently to tackle this problem? Thanks heaps!
June 30, 2021 at 8:46 pm
Heh... ok. Been there and done that... That's how it always starts. 😀
The other thing is that someone that gets desperate for some code on something much larger, might end up using that as a model and then you'll have some fun. 😀
Just sayin'... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply