1.在查询语句编写过程中,常常会遇到将类别相同的几个字段合并到一个字段的情况,今天就给大家分享在SQL Server中的字段进行合并的操作。
现有表:
(原始表)
(合并opinion列后)
实现代码:STUFF() 函数结合 For xml path 参数实现字段合并
select msWeek,opinion = ( stuff((select '|' + opinion from MenuSa where msWeek = A.msWeek for xml path('')),1,1,''))
from MenuSa as A group by msWeek
2.按分类求列的平均值
要求:得到msWeek(周期)内 menusa foodsa servicesa的平均值
(原始表)
(查询后)
实现代码:
select msWeek,avg(menuSa) as 总体,avg(foodSa) as 菜品 ,avg(serviceSa) as 服务 from MenuSa group by msWeek