SQL return relationship data on the same line

Asked

Viewed 86 times

-1

When carrying out the consultation 

SELECT [Product_Id]
      ,[ProductTag_Id]
  FROM [Product_ProductTag_Mapping]

I have the result

Resultado

I need Saida to be:

Resultado esperado

sql sql-server
  • 1

    look for pivot table, here are some answers on that

  • Obrigado Ricardo!

  • @Ricardopunctual in fact the PIVOT only solve if he wanted a column per result, but in case he just wants to concatenate the results. For this he should use something like the STRING_AGG.

  • But @Sorack works by grouping and concatenating? That’s interesting, I’ll see ;)

  • @Ricardopunctual yes, but only from the 2017 version of SQL Server

1 answer

0

As of 2017 version of SQL Server you can use the function STRING_AGG that allows grouping and concatenating results:

SELECT pptm.product_id,
       STRING_AGG(pptm.producttag_id, ', ') AS producttag_id
  FROM Product_ProductTag_Mapping pptm
 GROUP BY pptm.product_id;

For previous versions you can create a function to concatenate the results by product_id:

CREATE FUNCTION fn_tgs(@product_id INT)
  RETURNS NVARCHAR(MAX)
AS
BEGIN
  DECLARE @text NVARCHAR(MAX) = '';

  SELECT @text = ISNULL(@text + ',', '') + pptm.producttag_id
    FROM Product_ProductTag_Mapping pptm
   WHERE pptm.product_id = @product_id;

  RETURN @text;
END;

And use as follows:

SELECT pptm.product_id,
       fn_tgs(pptm.producttag_id) AS producttag_id
  FROM Product_ProductTag_Mapping pptm
 GROUP BY pptm.product_id;

STRING_AGG

Concatenate the values of the string expressions and place the separator values between them. Separator is not added at the end of the string.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.