RESOLVED | With a QUERY | CTE or a Sub-Query for Cross-tab Concatenate

  • 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.

    1. Product_main table joins with Product_helper on product_id.
    2. The above can join with product_allocation on promo_id

    -- 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

    */

     

     

     

     

    • This topic was modified 2 years, 9 months ago by  sqlguru1day.
    Attachments:
    You must be logged in to view attached files.
  • 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

  • Thanks Thom but, I still get the duplication in the last column promo group. How can I only have unique promo groups as a result? Thanks a ton,

     

    Attachments:
    You must be logged in to view attached files.
  • 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

     

  • Thanks - that's perfect!

  • sqlguru1day wrote:

    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


    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)

  • sqlguru1day wrote:

    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


    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)

  • 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!

  • 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


    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)

Viewing 9 posts - 1 through 8 (of 8 total)

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