TSQL How to concatenate column from joined table with group by

Question

I have 2 connected tables: Producto and Productos_ProductosRelacionados

    Producto                Productos_ProductosRelacionados
|id|referencia|         |id|idProducto|idProductoRelacionado|
|1 |    A     |         |1 |    1     |  2                  |
|2 |    B     |         |2 |    1     |  3                  |
|3 |    C     |         |3 |    3     |  4                  |
|4 |    D     |         |4 |    3     |  5                  |
|5 |    E     |

I need this:

|idProducto|referencia|
|   1      |  B,C     | 
|   2      |          | 
|   3      |  D,E     |

I have older SQL server so can not use STRING_AGG. So far I only achieve concatenating idProductoRelacionado:

|idProducto|idProductoRelacionado|
|   1      |  2,3                |

with:

SELECT pr1.idProducto
 ,STUFF((
          SELECT ',' + CONVERT(varchar, pr.idProductoRelacionado) 
          FROM [Productos_ProductosRelacionados] as pr
          WHERE pr.idProducto = pr1.idProducto        
          FOR XML PATH('')), 1, 1, '') as RelacionadosID
 FROM [dbo].[Producto] as p1 
 join [Productos_ProductosRelacionados] as pr1 on p1.id = pr1.idProductoRelacionado
 GROUP BY pr1.idProducto

If I try the same approach to concatenate referencia column it gives me: "Column 'dbo.Producto.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

select pr1.idProducto
,STUFF((
          SELECT ',' + p.referencia
          FROM [dbo].[Producto] as p
          WHERE p.id = p1.id  
          FOR XML PATH('')), 1, 1, '') as RelacionadosREF
 from [dbo].[Producto] as p1 
 join [Productos_ProductosRelacionados] as pr1 on p1.id = pr1.idProductoRelacionado
 GROUP BY pr1.idProducto

I do not understand the difference between 2 queries, why first one is working and second is not.

@ZoharPeled Yes, because I need concatenate filed from another table — Nick
– Nick, Commented Sep 22, 2018 at 10:57
Yes, but p1.Id is not in the group by clause - you can't use it like this. — Zohar Peled
– Zohar Peled, Commented Sep 22, 2018 at 10:59
@ZoharPeled you are right, that is obviously wrong but I did not come up with alternative :( — Nick
– Nick, Commented Sep 22, 2018 at 11:05
Tell you what. You edit your question to include sample data as DDL + DML, and I promise you are more likely to get a good answer soon. I currently have to go and I don't know when I'll be back on my computer but please ping me once it's done and I'll give it a go if you haven't already solved it. — Zohar Peled
– Zohar Peled, Commented Sep 22, 2018 at 11:47

Hary · Accepted Answer · 2018-09-22 12:47:14Z

2

Simple Join should work. Check SQLFiddle

SELECT 
  id,
  (SELECT
     cast (p1.referencia as varchar(100)) + ','
   FROM producto p
   LEFT JOIN Productos_ProductosRelacionados pr
         on pr.idProduct = p.id
   LEFT JOIN producto p1
         on p1.id = pr.idProductoRelacionado
   WHERE p.id = src.id
   FOR XML PATH('')) as referencia
FROM
   producto src;

answered Sep 22, 2018 at 12:47

Hary

5,8368 gold badges49 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PPJN · Accepted Answer · 2018-09-22 12:16:42Z

I ran into this a while back. You have to add a join to your stuff statement:

create table    #prod   (
                        id  int,
                        ref varchar(1)
                    )

insert into     #prod values
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D'),
(5,'E')

create table    #prod_rel   (
                                id      int,
                                pid     int,
                                id_rel  int
                            )

insert into     #prod_rel values
(1,1,2),
(2,1,3),
(3,3,4),
(4,3,5)



select  distinct
        pid
        ,STUFF(
                 (SELECT ',' + c.ref FROM #prod_rel b inner join #prod c on c.id = b.id_rel where b.pid = a.pid FOR XML PATH ('')), 1, 1, ''
               )
from    #prod_rel a

drop table  #prod
            ,#prod_rel

Esperento57 · Accepted Answer · 2018-09-22 20:41:07Z

You can use recursive query method, like this:

--Create tables for example
select * into #Producto from (
 select 1 id,'A' referential union all select 2,'B' union all select 3,'C' union all select 4,'D' union all select 5,'E'
 ) tmp;

select * into #Productos_ProductosRelacionados from (
select 1 id,1 idProducto,2 idProductoRelacionado union all select 2,1,3 union all select 3,3,4 union all select 4,3,5
) tmp;


-- Recurse query    
With tmp as (
select ROW_NUMBER() over(partition by f1.ID order by f1.id, f3.referential desc) RangID,
count(*)  over(partition by f1.ID order by f1.id) NbID, 
f1.id, f3.referential 
from #Producto f1
left outer join #Productos_ProductosRelacionados f2 on f1.id=f2.idProducto
left outer join #Producto f3 on f2.idProductoRelacionado=f3.id
),
Recurse as (
select f1.id, f1.RangID, f1.NbID, cast(f1.referential as varchar(2000)) referential, 1 rangrecur  from tmp f1 where RangID=1
union all
select f1.id, f1.RangID, f1.NbID, cast(isnull(f1.referential, '') + ',' + isnull(f2.referential, '') as varchar(2000)) referential, f2.rangrecur + 1 as rangrecur  
from tmp f1 inner join Recurse f2 on f1.id=f2.id and f1.RangID-1=f2.RangID
)
select ID, Referential from recurse
where NbID=rangrecur
order by ID;

Collectives™ on Stack Overflow

TSQL How to concatenate column from joined table with group by

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related