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

• sqlguru1day

  Old Hand

  Points: 356

  More actions

  June 3, 2021 at 7:31 am

  Go to Answer

  #3891332

  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.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  June 3, 2021 at 7:53 am

  Answer

  #3891337

  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

• sqlguru1day

  Old Hand

  Points: 356

  More actions

  June 3, 2021 at 10:36 pm

  #3891684

  Thanks heaps! That works 🙂