Using STUFF Function

Question

I've this table TableA that have these fields: [intIdEntidad],[intIdEjercicio],[idTipoGrupoCons]. The tableA look like for idTipoGrupoCons = 16 this image enter image description here

I'm trying to use STUFF function to show the column intIdEjercicio separated by coma, something like this; enter image description here

This is query I'm using to obtain result the above image:

SELECT DISTINCT o.idTipoGrupoCons, o.intIdEntidad, ejercicios= STUFF((
    SELECT ', ' + CONVERT(VARCHAR,a.intIdEjercicio)
    FROM dbo.[tbEntidades_Privadas_InfoAdicionalGrupo] AS a
   WHERE a.idTipoGrupoCons = 16 
    FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM [tbEntidades_Privadas_InfoAdicionalGrupo] AS o
JOIN tbEntidades_Privadas p On O.intIdEntidad = p.intIdEntidad 
WHERE o.idTipoGrupoCons = 16

The result isn't correct, because I execute this query for idTipoGrupoCons = 16

SELECT [idTipoGrupoCons], [intIdEntidad],[intIdEjercicio]
       FROM  [tbEntidades_Privadas_InfoAdicionalGrupo] A 
       WHERE A.idTipoGrupoCons = 16

The result is this enter image description here

It's means that for intIdEntidad = 50 intIdEjercicio is just 7 and for intIdEntidad = 45 intIdEjercicio = 2 and 4

I suppose that the problem is that I need to add a subquery to or a function into STUFF or in the outer WHERE to add condition to intIdEntidad each time to call STUFF function.

I've read about the use of CROSS APPLY and perhaps it can be used to solve the problem

score 0 · Answer 1 · answered Feb 05 '20 at 15:35

Here is the answer. The problem was that need to join tableA with the same table into the STUFF function. At the end the query look like this:

SELECT  t1.idTipoGrupoCons, t1.intIdEntidad,
        ,ejercicios =   STUFF(
                                    (SELECT ', ' + t3.Ejercicio
                                       FROM [tbEntidades_Privadas_InfoAdicionalGrupo] t2
                                       JOIN tbMtoNoRegistro_Ejercicios t3 ON t2.intIdEjercicio = e.intEjercicio
                                      WHERE t2.idTipoGrupoCons = t1.idTipoGrupoCons
                                        AND t2.intIdEntidad    = t1.intIdEntidad
                                     ORDER BY t3.Ejercicio
                                        FOR XML PATH ('')
                                    )
                            ,1,2,'')
   FROM [tbEntidades_Privadas_InfoAdicionalGrupo] t1
   JOIN tbEntidades_Privadas p ON t1.intIdEntidad = p.intIdEntidad
    WHERE t1.idTipoGrupoCons = 17
  GROUP BY t1.idTipoGrupoCons,t1.intIdEntidad, p.strDenominacionSocial

Using STUFF Function

1 Answers1