Cross-Tab & Concatenate Rows with a join : Help with a Query

  • Hi,

    Can someone please help me with a query?

    I have two tables and I want the concatenate rows as an output with a join on displayid. Please see attached desired output screenshot. How can I do that?

    -- Table #1

    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 (

    displayid int,

    promo_id int,

    promogroup varchar(255)

    );

    INSERT INTO Product_Helper SELECT 30275,5957,'Product A'

    INSERT INTO Product_Helper SELECT 30275,5958,'Product B'

    INSERT INTO Product_Helper SELECT 30275,5959,'Product C'

    INSERT INTO Product_Helper SELECT 30275,5960,'Product D'

    INSERT INTO Product_Helper SELECT 30270,1957,'Product P'

    INSERT INTO Product_Helper SELECT 30270,1958,'Product Q'

    INSERT INTO Product_Helper SELECT 30270,1959,'Product R'

    INSERT INTO Product_Helper SELECT 30270,1960,'Product S'

    GO;

    -- SELECT * FROM Product_Helper

    -- Table #2

    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 (

    displayid int,

    promo_id int,

    profile_id int

    );

    INSERT INTO Product_Main SELECT 30275,5957,3

    INSERT INTO Product_Main SELECT 30275,5958,3

    INSERT INTO Product_Main SELECT 30275,5959,3

    INSERT INTO Product_Main SELECT 30275,5960,3

    INSERT INTO Product_Main SELECT 30270,1957,3

    INSERT INTO Product_Main SELECT 30270,1958,3

    INSERT INTO Product_Main SELECT 30270,1959,3

    INSERT INTO Product_Main SELECT 30270,1960,3

    GO;

    -- SELECT * FROM Product_Main

    SELECT * FROM Product_Main

    SELECT * FROM Product_Helper

    /*

    SELECT *

    FROM Product_Helper Helper

    LEFT JOIN Product_Main Main ON Helper.displayid = Main.displayid

    */

    Attachments:
    You must be logged in to view attached files.
  • SELECT m.displayid, STRING_AGG(m.promo_id,';') AS Concat_Promo_Id, STRING_AGG(h.promogroup,';') AS Concat_promogroup
    FROM Product_Main m
    LEFT OUTER JOIN Product_Helper h ON h.displayid = m.displayid AND h.promo_id = m.promo_id
    GROUP BY m.displayid;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thanks heaps! That works 🙂

Viewing 3 posts - 1 through 3 (of 3 total)

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