例如：
产品 销量
A 6
B 3
C 5
A 1
B 2
C 3
想要显示结果
在一个字段中显示 A销量7台，B销量5台，C销量8台。
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-06-30 13:57:33
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([产品] varchar(1),[销量] int)
insert [tb]
select 'A',6 union all
select 'B',3 union all
select 'C',5 union all
select 'A',1 union all
select 'B',2 union all
select 'C',3
--------------开始查询--------------------------
select 产品,产品+'销量'+ltrim(sum(销量))+'台' as 销量 from tb group by 产品
declare @s varchar(1000)
;with f as
(
select 产品,产品+'销量'+ltrim(sum(销量))+'台' as 销量 from tb group by 产品
)
select @s=isnull(@s+',' , '')+销量 from f
select @s
----------------结果----------------------------
/* 产品 销量
---- -------------------
A A销量7台
B B销量5台
C C销量8台
(3 行受影响)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A销量7台,B销量5台,C销量8台
(1 行受影响)
*/
------解决方案--------------------
create table #yx
(产品 varchar(10),销量 int)
insert into #yx
select 'A',6 union all
select 'B',3 union all
select 'C',5 union all
select 'A',1 union all
select 'B',2 union all
select 'C',3
with t as
(select 产品,sum(销量) '销量'
from #yx
group by 产品)
select stuff((select ','+产品+'销量'+rtrim(销量)+'台'
from t
for xml path('')),1,1,'')+'.' '字段'
/*
字段
------------------------------
A销量7台,B销量5台,C销量8台.
(1 row(s) affected)
*/