Group a conversion list within SELECT

Question

I have a doubt in the SQL Server, I have a product table and another with a conversion list for each product I have a conversion list for that product. I need a product list with the conversion codes of that product as the image below each conversion separated by ** Those that do not have conversion code show blank

sql code

SELECT p.ID_Produto,p.CodigoInterno, p.Descricao, p.Localizacao, p.EstoqueAtual, c.IDProduto, c.Codigo FROM TB_Produtos AS p JOIN tb_produtoConversao AS c ON ID_Produto = IDProduto

does not appear products that have no conversion and shows separate tried to use groub by more error

select p.ID_Produto,p.CodigoInterno, p.Descricao, p.Localizacao, p.EstoqueAtual, c.IDProduto, c.Codigo from TB_Produtos as p join tb_produtoConversao as c on ID_Produto = IDProduto  GROUP BY p.ID_Produto

Table Products

CREATE TABLE [dbo].[TB_Produtos](
[ID_Produto] [int] IDENTITY(1,1) NOT NULL,
[CodigoInterno] [nvarchar](60) NULL,
[Localizacao] [nvarchar](50) NULL,
[Descricao] [nvarchar](150) NULL,
[CodigoCompra] [nvarchar](30) NULL,
[DescricaoCompra] [nvarchar](150) NULL,
[ValorCompraProduto] [decimal](18, 2) NOT NULL,
[DataUltimaEntrada] [datetime] NULL,
[DataUltimaSaida] [datetime] NULL,
[DescricaoFaturamento] [nvarchar](max) NULL,
[Marca] [nvarchar](30) NULL,
[EstoqueMinimo] [decimal](18, 2) NOT NULL,
[EstoqueAtual] [decimal](18, 2) NOT NULL,
[PrecoTabelaA] [decimal](18, 2) NOT NULL,
[PrecoTabelaB] [decimal](18, 2) NOT NULL,
[PrecoTabelaC] [decimal](18, 2) NOT NULL,
[AlicotaIBPT] [decimal](18, 2) NOT NULL,
[IVA] [decimal](18, 2) NOT NULL,
[ReducaoICMS] [decimal](18, 2) NOT NULL,
[AlicotaIPI] [decimal](18, 2) NOT NULL,
[Observacao] [nvarchar](max) NULL,
[DataInclusao] [datetime] NOT NULL,
[ID_UsuarioInclusao] [int] NOT NULL,
[DataAlteracao] [datetime] NULL,
[ID_UsuarioAlteracao] [int] NULL,
[Conversao] [nvarchar](30) NULL,
[ID_Unidade] [int] NOT NULL,
[GiroMensal] [decimal](18, 2) NOT NULL,
[IDUltimoFormecedorCompra] [int] NOT NULL,
[DataUltimaCompra] [datetime] NULL,
[NCM] [nvarchar](20) NULL,
[CST] [nvarchar](20) NULL,

Tabela Conversao

CREATE TABLE [dbo].[TB_ProdutoConversao](
[IDConversao] [int] IDENTITY(1,1) NOT NULL,
[Codigo] [nvarchar](max) NULL,
[Descricao] [nvarchar](max) NULL,
[DtExclusao] [datetime] NULL,
[IdUsuarioExcluiu] [int] NULL,
[DataInclusao] [datetime] NOT NULL,
[ID_UsuarioInclusao] [int] NOT NULL,
[DataAlteracao] [datetime] NULL,
[ID_UsuarioAlteracao] [int] NULL,
[IDProduto] [int] NOT NULL,

Would look like this:

codigo  **138SHPN150 ** KTB135-S ** DYT233**

Answer 1

Taking advantage of @Sorak’s reply here: how-to-concatenate-lines

You need to concatenate the multiple rows of the table Tb_productoconversao and add that to your SELECT. This can be done with JOIN, but a simple way is to make a subquery, that would be so:

select STRING_AGG(codigo,' ** ') from tb_produtoConversao
 group by idProduto;

This will return the values like this: "138SHPN150 ** KTB135-S ** DYT233"

See that for this example I used the Function STRING_AGG, if you are using SQL Server 2017+, but could do with SUBSTRING/FOR XML as in the other example of the link answer above.

Back to the query, just add this subquery to have the result:

select p.ID_Produto,
       p.CodigoInterno, 
       p.Descricao, 
       p.Localizacao, 
       p.EstoqueAtual, 
       c.IDProduto, 
       (select STRING_AGG(c2.codigo,',')
          from tb_produtoConversao c2
         where c2.IDProduto=c.IDProduto
         group by c2.IDProduto) as Codigos
  from TB_Produtos as p 
  join tb_produtoConversao as c on ID_Produto = IDProduto  
  group by p.ID_Produto,
       p.CodigoInterno, 
       p.Descricao, 
       p.Localizacao, 
       p.EstoqueAtual, 
       c.IDProduto

You can see it working here: http://sqlfiddle.com/#! 18/6d40d/15

Note that I only added the relevant columns and data to test the result. To be noted also:

• all fields outside the field of subquery were added to the group by;
• in subquery, done join of the "tb_productoConversion" with itself (alias C2 and c), to return the correct aggregated codes relative to the main query (tb_product c);

Answer 2

Your query has been adapted to generate a column named as codigos_conversao.

• For the "Those who do not have conversion code show blank" criterion Isnull was used (content,'')
• The field has been removed c.Codigo of the initial query that would show each code that is already being concatenated in the column codigos_conversao and then you can succeed with the Distinct of consultation.

Consultation below:

SELECT distinct p.ID_Produto,p.CodigoInterno, p.Descricao, p.Localizacao, p.EstoqueAtual, c.IDProduto,
 IsNull(
         (SELECT DISTINCT convert(varchar(10), C1.Codigo) + ' ** '  AS codigos
                    FROM TB_ProdutoConversao C1 
                    where C1.IDProduto = P.ID_Produto
                      For XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),''
        ) as codigos_conversao
FROM TB_Produtos AS p 
Left JOIN tb_produtoConversao AS c ON ID_Produto = IDProduto

EDIT:

Another possible solution would be to use a function that returns the desired concatenation, but would have to consider the performance between solutions.

Of course there are several ways to do, here I tried to help with the use of For XML PATH.

Before posting the answer, he had performed tests. These tests were published in SQL Fiddle and made available below also.

MS SQL Server 2017 Schema Setup:

/****** Object:  Table [dbo].[TB_Produtos]    Script Date: 11/04/2021 03:31:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TB_Produtos](
    [ID_Produto] [int] IDENTITY(1,1) NOT NULL,
    [CodigoInterno] [nvarchar](60) NULL,
    [Localizacao] [nvarchar](50) NULL,
    [Descricao] [nvarchar](150) NULL,
    [CodigoCompra] [nvarchar](30) NULL,
    [DescricaoCompra] [nvarchar](150) NULL,
    [ValorCompraProduto] [decimal](18, 2) NOT NULL,
    [DataUltimaEntrada] [datetime] NULL,
    [DataUltimaSaida] [datetime] NULL,
    [DescricaoFaturamento] [nvarchar](max) NULL,
    [Marca] [nvarchar](30) NULL,
    [EstoqueMinimo] [decimal](18, 2) NOT NULL,
    [EstoqueAtual] [decimal](18, 2) NOT NULL,
    [PrecoTabelaA] [decimal](18, 2) NOT NULL,
    [PrecoTabelaB] [decimal](18, 2) NOT NULL,
    [PrecoTabelaC] [decimal](18, 2) NOT NULL,
    [AlicotaIBPT] [decimal](18, 2) NOT NULL,
    [IVA] [decimal](18, 2) NOT NULL,
    [ReducaoICMS] [decimal](18, 2) NOT NULL,
    [AlicotaIPI] [decimal](18, 2) NOT NULL,
    [Observacao] [nvarchar](max) NULL,
    [DataInclusao] [datetime] NOT NULL,
    [ID_UsuarioInclusao] [int] NOT NULL,
    [DataAlteracao] [datetime] NULL,
    [ID_UsuarioAlteracao] [int] NULL,
    [Conversao] [nvarchar](30) NULL,
    [ID_Unidade] [int] NOT NULL,
    [GiroMensal] [decimal](18, 2) NOT NULL,
    [IDUltimoFormecedorCompra] [int] NOT NULL,
    [DataUltimaCompra] [datetime] NULL,
    [NCM] [nvarchar](20) NULL,
    [CST] [nvarchar](20) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];


INSERT INTO TB_Produtos (CodigoInterno,Localizacao,Descricao,CodigoCompra,DescricaoCompra,ValorCompraProduto,DataUltimaEntrada,DataUltimaSaida,DescricaoFaturamento,Marca,EstoqueMinimo,EstoqueAtual,PrecoTabelaA,PrecoTabelaB,PrecoTabelaC,AlicotaIBPT,IVA,ReducaoICMS,AlicotaIPI,Observacao,DataInclusao,ID_UsuarioInclusao,DataAlteracao,ID_UsuarioAlteracao,Conversao,ID_Unidade,GiroMensal,IDUltimoFormecedorCompra,DataUltimaCompra,NCM,CST) VALUES
     ('41.34605','PRATELEIRA A1','CORREIA DENTADA',NULL,NULL,10.00,NULL,NULL,NULL,NULL,2.00,4.00,3.00,2.00,1.00,1.00,1.00,1.00,1.00,NULL,'2021-04-10 00:00:00.000',1,NULL,NULL,NULL,1,25.00,125,NULL,NULL,NULL),
     ('4654','PRATELEIRA B1','JOGO DE JUNTAS MOTOR',NULL,NULL,12.00,NULL,NULL,NULL,NULL,10.00,30.00,12.00,10.00,8.00,1.00,1.00,1.00,1.00,NULL,'2021-04-10 00:00:00.000',1,NULL,NULL,NULL,1,25.00,125,NULL,NULL,NULL);
;

CREATE TABLE TB_ProdutoConversao (
    IDConversao int IDENTITY(1,1) NOT NULL,
    Codigo nvarchar(max) COLLATE Latin1_General_CI_AS NULL,
    Descricao nvarchar(max) COLLATE Latin1_General_CI_AS NULL,
    DtExclusao datetime NULL,
    IdUsuarioExcluiu int NULL,
    DataAlteracao datetime NULL,
    ID_UsuarioAlteracao int NULL,
    IDProduto int NOT NULL
);

INSERT INTO TB_ProdutoConversao (Codigo,Descricao,DtExclusao,IdUsuarioExcluiu,DataAlteracao,ID_UsuarioAlteracao,IDProduto) VALUES
     ('138SHPN150',NULL,NULL,NULL,NULL,NULL,1),
     ('KTB135-S',NULL,NULL,NULL,NULL,NULL,1),
     ('DYT233',NULL,NULL,NULL,NULL,NULL,1);

Query 1:

select * from TB_Produtos

Results:

| ID_Produto | CodigoInterno |   Localizacao |            Descricao | CodigoCompra | DescricaoCompra | ValorCompraProduto | DataUltimaEntrada | DataUltimaSaida | DescricaoFaturamento |  Marca | EstoqueMinimo | EstoqueAtual | PrecoTabelaA | PrecoTabelaB | PrecoTabelaC | AlicotaIBPT | IVA | ReducaoICMS | AlicotaIPI | Observacao |         DataInclusao | ID_UsuarioInclusao | DataAlteracao | ID_UsuarioAlteracao | Conversao | ID_Unidade | GiroMensal | IDUltimoFormecedorCompra | DataUltimaCompra |    NCM |    CST |
|------------|---------------|---------------|----------------------|--------------|-----------------|--------------------|-------------------|-----------------|----------------------|--------|---------------|--------------|--------------|--------------|--------------|-------------|-----|-------------|------------|------------|----------------------|--------------------|---------------|---------------------|-----------|------------|------------|--------------------------|------------------|--------|--------|
|          1 |      41.34605 | PRATELEIRA A1 |      CORREIA DENTADA |       (null) |          (null) |                 10 |            (null) |          (null) |               (null) | (null) |             2 |            4 |            3 |            2 |            1 |           1 |   1 |           1 |          1 |     (null) | 2021-04-10T00:00:00Z |                  1 |        (null) |              (null) |    (null) |          1 |         25 |                      125 |           (null) | (null) | (null) |
|          2 |          4654 | PRATELEIRA B1 | JOGO DE JUNTAS MOTOR |       (null) |          (null) |                 12 |            (null) |          (null) |               (null) | (null) |            10 |           30 |           12 |           10 |            8 |           1 |   1 |           1 |          1 |     (null) | 2021-04-10T00:00:00Z |                  1 |        (null) |              (null) |    (null) |          1 |         25 |                      125 |           (null) | (null) | (null) |

Query 2:

select * from TB_ProdutoConversao

Results:

| IDConversao |     Codigo | Descricao | DtExclusao | IdUsuarioExcluiu | DataAlteracao | ID_UsuarioAlteracao | IDProduto |
|-------------|------------|-----------|------------|------------------|---------------|---------------------|-----------|
|           1 | 138SHPN150 |    (null) |     (null) |           (null) |        (null) |              (null) |         1 |
|           2 |   KTB135-S |    (null) |     (null) |           (null) |        (null) |              (null) |         1 |
|           3 |     DYT233 |    (null) |     (null) |           (null) |        (null) |              (null) |         1 |

Query 3 - Proposal as answer to the question:

SELECT distinct p.ID_Produto,p.CodigoInterno, p.Descricao, p.Localizacao, p.EstoqueAtual, c.IDProduto,
 IsNull(
         (SELECT DISTINCT convert(varchar(10), C1.Codigo) + ' ** '  AS codigos
                    FROM TB_ProdutoConversao C1 
                    where C1.IDProduto = P.ID_Produto
                      For XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),''
        ) as codigos_conversao
FROM TB_Produtos AS p 
Left JOIN tb_produtoConversao AS c ON ID_Produto = IDProduto

Results:

| ID_Produto | CodigoInterno |            Descricao |   Localizacao | EstoqueAtual | IDProduto |                    codigos_conversao |
|------------|---------------|----------------------|---------------|--------------|-----------|--------------------------------------|
|          1 |      41.34605 |      CORREIA DENTADA | PRATELEIRA A1 |            4 |         1 | 138SHPN150 ** DYT233 ** KTB135-S **  |
|          2 |          4654 | JOGO DE JUNTAS MOTOR | PRATELEIRA B1 |           30 |    (null) |                                      |