Concatenate in Group By ?

[stepone]

Hi,

I am running a Group By query and want to get one of the text fields to concatenate (rather than pick First or Last). So, for example, if the data looked like this ;

Project    Job         JobType          Cost
1            1            Red                45
2            1            Red                45
2            2            Blue               20

I want to get a single entry for each project, so the output from the query would read ;

Project   Job Type          SumOfCost      
1           Red                 45
2           Red & Blue       65

Alternatively, if it was possible to output the text 'Various' or similar anywhere there was more than one Job Type, that would also be an option, so the output would look as follows ;

Project   Job Type          SumOfCost      
1           Red                 45
2           Various       65

It seems like this should be straightforward, I am probably missing something simple !

Thanks in advance,
StepOne

 

[pbaldy]

One way would be a function like this:

http://allenbrowne.com/func-concat.html

 

[namliam]

select project
, iif(count(*)>1, "Various", Jobtype)
, sum(cost)
from yourtable
group by project

Would that work? I am not sure...