GROUP BY with concatenated values in select list

Question

I'm having a problem getting the following SELECT to run:

SET @final = '<CoutUnitaire>'

SELECT  DISTINCT
    @final = COALESCE(@final +  '', ',') + 
    '<row><Intervention>' + Code_Type_Mode + ' ' + Code_Complement + ' ' + Code_Phytocide + '</Intervention>' + 
    '<cout_moyen>' + CAST(CAST(AVG(Travail.cout_par_ha) AS DECIMAL(10,2)) AS VARCHAR) + '</cout_moyen>' +
    '</row>' 
FROM Travail 
    INNER JOIN Budget ON Travail.ID_Budget = Budget.ID_Budget 
    INNER JOIN Territoire ON Budget.ID_Territoire = Territoire.ID_Territoire 
    INNER JOIN Complement ON Travail.ID_Complement = Complement.ID_Complement 
    INNER JOIN Phytocide ON Travail.ID_Phytocide = Phytocide.ID_Phytocide 
    INNER JOIN Type_Mode ON Travail.ID_Type_Mode = Type_Mode.ID_Type_Mode 
    INNER JOIN #Years ON Budget.Annee_Budgetaire = #Years.intYear
WHERE dbo.Budget.ID_Territoire IN (SELECT intTerritoryID FROM #Territories) AND (@circref = 0 OR circref = @circref)
GROUP BY Code_Type_Mode + ' ' + Code_Complement + ' ' + Code_Phytocide
ORDER BY Code_Type_Mode + ' ' + Code_Complement + ' ' + Code_Phytocide

SET @final = @final + '</CoutUnitaire>'

It returns:

Column 'Type_Mode.Code_Type_Mode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'Complement.Code_Complement' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'Phytocide.Code_Phytocide' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

---

Limitation:

Running SQL Server 2000, so I don't have the option of populating this variable using FOR XML. Need to build it manually.

How do I go about getting these rows in the right order, while still concatenating them into a string?

Answer 1

When I play with this, I find that you can make it work as written by putting the whole select string in the Group By.
I'm not sure that's the best way to go, but a simplified case below shows it.

I wonder if you might get better performance - and maintenance - by separating building that xml output with COALESCE from the rest of it.

Could you do your joins and pull the Avg() and 3 string fields into a temp table, and then do the COALESCE trick to build your output string?

---

.... showing a direct fix of this case

/*
drop table a
drop table b
drop table c
*/    

create table a (
    id int
    ,dataKey int
)
;

create table b (
    dataKey int
    ,dataVal varchar(100)
)
;

create table c (
    dataKey int
    ,dataVal varchar(100)
)
;

-----
insert into a
(id, dataKey)
values
(1, 1)
;
insert into a
(id, dataKey)
values
(2, 2)
;

-----
insert into b
(dataKey, dataVal)
values
(1, 'asdf')
;
insert into c
(dataKey, dataVal)
values
(1, 'jkl;')
;



declare @final varchar(1000);
set @final = 'start....';

select
    @final = 
        coalesce(@final + ',', ',')
        + 
        b.dataval
        + ' ' 
        + c.dataval
from
    a
        inner join b on a.dataKey = b.dataKey
        inner join c on a.dataKey = c.dataKey
group by
        coalesce(@final + ',', ',')
        + 
        b.dataval
        + ' ' 
        + c.dataval

print @final