+--------+------------+-------+ | type | variety | price | +--------+------------+-------+ | apple | gala | 2.79 | | apple | fuji | 0.24 | | apple | limbertwig | 2.87 | | orange | valencia | 3.59 | | orange | navel | 9.36 | | pear | bradford | 6.05 | | pear | bartlett | 2.14 | | cherry | bing | 2.55 | | cherry | chelan | 6.33 | +--------+------------+-------+
+--------+----------------------------+-------+ | type | variety | price | +--------+----------------------------+-------+ | apple | gala, fuji, limbertwig | 5.9 | | orange | valencia,navel | 12.95 | | pear | bradford,bartlett | 8.19 | | cherry | bing, chelan | 8.88 | +--------+----------------------------+-------+
SELECT Type, Variety = STUFF((SELECT ',' + Variety FROM myTable b WHERE b.Type = a.Type FOR XML PATH('')), 1, 1, ''), SUM(Price) AS Price FROM myTable a GROUP BY Type
Variety
FOR XML PATH(''), TYPE
.value('.', 'varchar(max)')
SELECT Type, Variety = STUFF((SELECT ',' + Variety FROM myTable b WHERE b.Type = a.Type FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''), SUM(Price) AS PriceFROM myTable aGROUP BY Type
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)