日期:2014-05-17 浏览次数:20501 次
CREATE TABLE [dbo].[Table_1](
[ID] [nchar](10) NULL,
[a] [int] NULL,
[DocNo] [nchar](10) NULL,
[num] [int] NULL
) ON [PRIMARY]
insert into table_1
select 1,177,101,5
union all select 2,178,102,5
union all select 3,177,103,5
union all select 4,179,104,5
union all select 5,179,105,5
select SUM(num) as SUM_num,a FROM table_1
group by a
SUM_num a
10 177
5 178
10 179
SUM_num a 列名ccc
10 177 101,103
5 178 102
10 179 104,105
select SUM(num) as SUM_num,a ,
ccc=STUFF((SELECT ',' + RTRIM(DocNo) FROM dbo.table_1 b WHERE b.a=a.a FOR XML PATH('')),1,1,'')
FROM table_1 a
group by a
/*
SUM_num a ccc
10 177 101,103
5 178 102
10 179 104,105
*/
select sum(x.num) 'SUM_num',
x.a,
stuff((select ','+rtrim(y.DocNo)