大概就是这样,楼主试试行不行
select 客户名称, 付款方式, p033, p022, p011, 合计
from (
select 付款方式 as 排序字段, 客户名称, 付款方式,
(case 品种名称 when 'p033' then 数量 else 0 end) as p033,
(case 品种名称 when 'p022' then 数量 else 0 end) as p022,
(case 品种名称 when 'p011' then 数量 else 0 end) as p011,
数量 as 合计
from tablename
union all
select 付款方式 + 'a', '小计', '',
sum(case 品种名称 when 'p033' then 数量 else 0 end) as p033,
sum(case 品种名称 when 'p022' then 数量 else 0 end) as p022,
sum(case 品种名称 when 'p011' then 数量 else 0 end) as p011,
sum(数量) as 合计
from tablename
group by 付款方式
union all
select max(付款方式) + 'b', '合计', '',
sum(case 品种名称 when 'p033' then 数量 else 0 end) as p033,
sum(case 品种名称 when 'p022' then 数量 else 0 end) as p022,
sum(case 品种名称 when 'p011' then 数量 else 0 end) as p011,
sum(数量) as 合计
from tablename)
order by 排序字段, 客户名称
select 客户名称, 付款方式, p033, p022, p011, 合计
from (
select 付款方式 as 排序字段, 客户名称, 付款方式,
(case 品种名称 when 'p033' then 数量 else 0 end) as p033,
(case 品种名称 when 'p022' then 数量 else 0 end) as p022,
(case 品种名称 when 'p011' then 数量 else 0 end) as p011,
数量 as 合计
from tablename
union all
select 付款方式 + 'a', '小计', '',
sum(case 品种名称 when 'p033' then 数量 else 0 end) as p033,
sum(case 品种名称 when 'p022' then 数量 else 0 end) as p022,
sum(case 品种名称 when 'p011' then 数量 else 0 end) as p011,
sum(数量) as 合计
from tablename
group by 付款方式
union all
select max(付款方式) + 'b', '合计', '',
sum(case 品种名称 when 'p033' then 数量 else 0 end) as p033,
sum(case 品种名称 when 'p022' then 数量 else 0 end) as p022,
sum(case 品种名称 when 'p011' then 数量 else 0 end) as p011,
sum(数量) as 合计
from tablename)
order by 排序字段, 客户名称
if object_id('tablename') is not null drop table tablename
select 'aaa' as 客户名称, 'p033' as 品种名称, 3 as 数量, '现金' as 付款方式
into tablename
union all select 'aaa', 'p033', 5, '预付'
union all select 'bbb', 'p022', 4, '预付'
union all select 'ccc', 'p011', 10, '现金'
union all select 'bbb', 'p033', 10, '现金'select * from tablename
/*
aaa p033 3 现金
aaa p033 5 预付
bbb p022 4 预付
ccc p011 10 现金
bbb p033 10 现金
*/select 客户名称, 付款方式, p033, p022, p011, 合计
from (
select 付款方式 as 排序字段, 客户名称, 付款方式,
(case 品种名称 when 'p033' then 数量 else 0 end) as p033,
(case 品种名称 when 'p022' then 数量 else 0 end) as p022,
(case 品种名称 when 'p011' then 数量 else 0 end) as p011,
数量 as 合计
from tablename
union all
select 付款方式 + 'a', '小计', '',
sum(case 品种名称 when 'p033' then 数量 else 0 end),
sum(case 品种名称 when 'p022' then 数量 else 0 end),
sum(case 品种名称 when 'p011' then 数量 else 0 end),
sum(数量) as 合计
from tablename
group by 付款方式
union all
select max(付款方式) + 'b', '合计', '',
sum(case 品种名称 when 'p033' then 数量 else 0 end),
sum(case 品种名称 when 'p022' then 数量 else 0 end),
sum(case 品种名称 when 'p011' then 数量 else 0 end),
sum(数量) as 合计
from tablename) a
order by 排序字段, 客户名称
/*
aaa 现金 3 0 0 3
bbb 现金 10 0 0 10
ccc 现金 0 0 10 10
小计 13 0 10 23
aaa 预付 5 0 0 5
bbb 预付 0 4 0 4
小计 5 4 0 9
合计 18 4 10 32
*/drop table tablename
from (
select 付款方式 as 排序字段, 客户名称, 付款方式,
(case 品种名称 when 'p033' then 数量 else 0 end) as p033,
(case 品种名称 when 'p022' then 数量 else 0 end) as p022,
(case 品种名称 when 'p011' then 数量 else 0 end) as p011,
数量 as 合计
from tablename
union all
select 付款方式 + 'a', '小计', '',
sum(case 品种名称 when 'p033' then 数量 else 0 end),
sum(case 品种名称 when 'p022' then 数量 else 0 end),
sum(case 品种名称 when 'p011' then 数量 else 0 end),
sum(数量) as 合计
from tablename
group by 付款方式
union all
select max(付款方式) + 'b', '合计', '',
sum(case 品种名称 when 'p033' then 数量 else 0 end),
sum(case 品种名称 when 'p022' then 数量 else 0 end),
sum(case 品种名称 when 'p011' then 数量 else 0 end),
sum(数量) as 合计
from tablename) a
order by 排序字段, 客户名称
可惜我不会关注,学习
select 'aaa' as 客户名称, 'p033' as 品种名称, 3 as 数量, '现金' as 付款方式
into tablename
union all select 'aaa', 'p033', 5, '预付'
union all select 'bbb', 'p022', 4, '预付'
union all select 'ccc', 'p011', 10, '现金'
union all select 'bbb', 'p033', 10, '现金'select * from tablename
/*
aaa p033 3 现金
aaa p033 5 预付
bbb p022 4 预付
ccc p011 10 现金
bbb p033 10 现金
*/--品种名称不固定
declare @s varchar(8000)
set @s = 'select 客户名称, 付款方式, '
select @s = @s + 品种名称 + ', ' from (select distinct 品种名称 from tablename) a
set @s = @s + '合计 from (select 付款方式 as 排序字段, 客户名称, 付款方式, '
select @s = @s + '(case 品种名称 when ''' + 品种名称 + ''' then 数量 else 0 end) as ' + 品种名称 + ', '
from (select distinct 品种名称 from tablename) a
set @s = @s + '数量 as 合计 from tablename union all select 付款方式 + ''a'', ''小计'', '''', '
select @s = @s + 'sum(case 品种名称 when ''' + 品种名称 + ''' then 数量 else 0 end), '
from (select distinct 品种名称 from tablename) a
set @s = @s + 'sum(数量) as 合计 from tablename group by 付款方式 ' +
'union all select max(付款方式) + ''b'', ''合计'', '''', '
select @s = @s + 'sum(case 品种名称 when ''' + 品种名称 + ''' then 数量 else 0 end), '
from (select distinct 品种名称 from tablename) a
set @s = @s + 'sum(数量) as 合计 from tablename) a order by 排序字段, 客户名称'
exec(@s)
/*
aaa 现金 0 0 3 3
bbb 现金 0 0 10 10
ccc 现金 10 0 0 10
小计 10 0 13 23
aaa 预付 0 0 5 5
bbb 预付 0 4 0 4
小计 0 4 5 9
合计 10 4 18 32
*/--品种名称固定
select 客户名称, 付款方式, p033, p022, p011, 合计
from (
select 付款方式 as 排序字段, 客户名称, 付款方式,
(case 品种名称 when 'p033' then 数量 else 0 end) as p033,
(case 品种名称 when 'p022' then 数量 else 0 end) as p022,
(case 品种名称 when 'p011' then 数量 else 0 end) as p011,
数量 as 合计
from tablename
union all
select 付款方式 + 'a', '小计', '',
sum(case 品种名称 when 'p033' then 数量 else 0 end),
sum(case 品种名称 when 'p022' then 数量 else 0 end),
sum(case 品种名称 when 'p011' then 数量 else 0 end),
sum(数量) as 合计
from tablename
group by 付款方式
union all
select max(付款方式) + 'b', '合计', '',
sum(case 品种名称 when 'p033' then 数量 else 0 end),
sum(case 品种名称 when 'p022' then 数量 else 0 end),
sum(case 品种名称 when 'p011' then 数量 else 0 end),
sum(数量) as 合计
from tablename) a
order by 排序字段, 客户名称
/*
aaa 现金 3 0 0 3
bbb 现金 10 0 0 10
ccc 现金 0 0 10 10
小计 13 0 10 23
aaa 预付 5 0 0 5
bbb 预付 0 4 0 4
小计 5 4 0 9
合计 18 4 10 32
*/drop table tablename
if exists(select * from sysobjects where name='table1') drop table table1
create table table1(customer varchar(3),product varchar(4),num int,pay varchar(4))
insert into table1 values('aaa','p033',3,'cash')
insert into table1 values('aaa','p033',5,'befo')
insert into table1 values('bbb','p022',4,'befo')
insert into table1 values('ccc','p011',10,'cash')
insert into table1 values('bbb','p033',10,'cash')select customer,product,sum(num),pay from table1
group by customer,product,pay with cube
select 'aaa' as 客户名称, 'a/#$%&,a ' as 品种名称, 3 as 数量, '现金' as 付款方式
into tablename
union all select 'aaa', 'a/#$%&,a ', 5, '预付'
union all select 'bbb', 'p022', 4, '预付'
union all select 'ccc', 'p011', 10, '现金'
union all select 'bbb', 'a/#$%&,a ', 10, '现金'select * from tablename
/*
客户名称 品种名称 数量 付款方式
aaa a/#$%&,a 3 现金
aaa a/#$%&,a 5 预付
bbb p022 4 预付
ccc p011 10 现金
bbb a/#$%&,a 10 现金
*/--品种名称不固定
declare @s varchar(8000)
set @s = 'select 客户名称, 付款方式, '
select @s = @s + '[' + 品种名称 + '], '
from (select distinct 品种名称 from tablename) a
set @s = @s + '合计 from (select 付款方式 as 排序字段, 客户名称, 付款方式, '
select @s = @s + '(case 品种名称 when ''' + 品种名称 + ''' then 数量 else 0 end) as [' + 品种名称 + '], '
from (select distinct 品种名称 from tablename) a
set @s = @s + '数量 as 合计 from tablename union all select 付款方式 + ''a'', ''小计'', '''', '
select @s = @s + 'sum(case 品种名称 when ''' + 品种名称 + ''' then 数量 else 0 end), '
from (select distinct 品种名称 from tablename) a
set @s = @s + 'sum(数量) as 合计 from tablename group by 付款方式 ' +
'union all select max(付款方式) + ''b'', ''合计'', '''', '
select @s = @s + 'sum(case 品种名称 when ''' + 品种名称 + ''' then 数量 else 0 end), '
from (select distinct 品种名称 from tablename) a
set @s = @s + 'sum(数量) as 合计 from tablename) a order by 排序字段, 客户名称'
exec(@s)
/*
客户名称 付款方式 p011 p022 a/#$%&,a 合计
aaa 现金 0 0 3 3
bbb 现金 0 0 10 10
ccc 现金 10 0 0 10
小计 10 0 13 23
aaa 预付 0 0 5 5
bbb 预付 0 4 0 4
小计 0 4 5 9
合计 10 4 18 32
*/--品种名称固定
select 客户名称, 付款方式, [a/#$%&,a ], [p022], [p011], 合计
from (
select 付款方式 as 排序字段, 客户名称, 付款方式,
(case 品种名称 when 'a/#$%&,a ' then 数量 else 0 end) as [a/#$%&,a ],
(case 品种名称 when 'p022' then 数量 else 0 end) as [p022],
(case 品种名称 when 'p011' then 数量 else 0 end) as [p011],
数量 as 合计
from tablename
union all
select 付款方式 + 'a', '小计', '',
sum(case 品种名称 when 'a/#$%&,a ' then 数量 else 0 end),
sum(case 品种名称 when 'p022' then 数量 else 0 end),
sum(case 品种名称 when 'p011' then 数量 else 0 end),
sum(数量) as 合计
from tablename
group by 付款方式
union all
select max(付款方式) + 'b', '合计', '',
sum(case 品种名称 when 'a/#$%&,a ' then 数量 else 0 end),
sum(case 品种名称 when 'p022' then 数量 else 0 end),
sum(case 品种名称 when 'p011' then 数量 else 0 end),
sum(数量) as 合计
from tablename) a
order by 排序字段, 客户名称
/*
客户名称 付款方式 a/#$%&,a p022 p011 合计
aaa 现金 3 0 0 3
bbb 现金 10 0 0 10
ccc 现金 0 0 10 10
小计 13 0 10 23
aaa 预付 5 0 0 5
bbb 预付 0 4 0 4
小计 5 4 0 9
合计 18 4 10 32
*/drop table tablename
select cast('2004-01-01' as datetime) as 日期, 'aaa' as 客户名称, 'a/#$%&,a ' as 品种名称, 3 as 数量, '现金' as 付款方式
into tablename
union all select '2004-02-01', 'aaa', 'a/#$%&,a ', 5, '预付'
union all select '2004-03-01', 'bbb', 'p022', 4, '预付'
union all select '2004-04-01', 'ccc', 'p011', 10, '现金'
union all select '2004-05-01', 'bbb', 'a/#$%&,a ', 10, '现金'select * from tablename
/*
日期 客户名称 品种名称 数量 付款方式
2004-01-01 00:00:00.000 aaa a/#$%&,a 3 现金
2004-02-01 00:00:00.000 aaa a/#$%&,a 5 预付
2004-03-01 00:00:00.000 bbb p022 4 预付
2004-04-01 00:00:00.000 ccc p011 10 现金
2004-05-01 00:00:00.000 bbb a/#$%&,a 10 现金
*/declare @sdate datetime, @edate datetime
set @sdate = '2004-02-01'
set @edate = '2004-04-01'
--品种名称不固定
declare @s varchar(8000)
set @s = 'select 客户名称, 付款方式, '
select @s = @s + '[' + 品种名称 + '], '
from (select distinct 品种名称 from tablename) a
set @s = @s + '合计 from (select 付款方式 as 排序字段, 客户名称, 付款方式, '
select @s = @s + '(case 品种名称 when ''' + 品种名称 + ''' then 数量 else 0 end) as [' + 品种名称 + '], '
from (select distinct 品种名称 from tablename) a
set @s = @s + '数量 as 合计 from tablename where 日期 between ''' +
convert(varchar(10), @sdate, 120) + ''' and ''' +
convert(varchar(10), @edate, 120) +
''' union all select 付款方式 + ''a'', ''小计'', '''', '
select @s = @s + 'sum(case 品种名称 when ''' + 品种名称 + ''' then 数量 else 0 end), '
from (select distinct 品种名称 from tablename) a
set @s = @s + 'sum(数量) as 合计 from tablename where 日期 between ''' +
convert(varchar(10), @sdate, 120) + ''' and ''' +
convert(varchar(10), @edate, 120) +
''' group by 付款方式 ' +
'union all select max(付款方式) + ''b'', ''合计'', '''', '
select @s = @s + 'sum(case 品种名称 when ''' + 品种名称 + ''' then 数量 else 0 end), '
from (select distinct 品种名称 from tablename) a
set @s = @s + 'sum(数量) as 合计 from tablename where 日期 between ''' +
convert(varchar(10), @sdate, 120) + ''' and ''' +
convert(varchar(10), @edate, 120) +
''') a order by 排序字段, 客户名称'
exec(@s)
/*
客户名称 付款方式 a/#$%&,a p011 p022 合计
ccc 现金 0 10 0 10
小计 0 10 0 10
aaa 预付 5 0 0 5
bbb 预付 0 0 4 4
小计 5 0 4 9
合计 5 10 4 19
*/--品种名称固定
select 客户名称, 付款方式, [a/#$%&,a ], [p022], [p011], 合计
from (
select 付款方式 as 排序字段, 客户名称, 付款方式,
(case 品种名称 when 'a/#$%&,a ' then 数量 else 0 end) as [a/#$%&,a ],
(case 品种名称 when 'p022' then 数量 else 0 end) as [p022],
(case 品种名称 when 'p011' then 数量 else 0 end) as [p011],
数量 as 合计
from tablename
where 日期 between '2004-02-01' and '2004-04-01'
union all
select 付款方式 + 'a', '小计', '',
sum(case 品种名称 when 'a/#$%&,a ' then 数量 else 0 end),
sum(case 品种名称 when 'p022' then 数量 else 0 end),
sum(case 品种名称 when 'p011' then 数量 else 0 end),
sum(数量) as 合计
from tablename
where 日期 between '2004-02-01' and '2004-04-01'
group by 付款方式
union all
select max(付款方式) + 'b', '合计', '',
sum(case 品种名称 when 'a/#$%&,a ' then 数量 else 0 end),
sum(case 品种名称 when 'p022' then 数量 else 0 end),
sum(case 品种名称 when 'p011' then 数量 else 0 end),
sum(数量) as 合计
from tablename
where 日期 between '2004-02-01' and '2004-04-01') a
order by 排序字段, 客户名称
/*
客户名称 付款方式 a/#$%&,a p022 p011 合计
ccc 现金 0 10 0 10
小计 0 10 0 10
aaa 预付 5 0 0 5
bbb 预付 0 0 4 4
小计 5 0 4 9
合计 5 10 4 19
*/drop table tablename
select 'aaa' as 客户名称, 'a/#$%&,a ' as 品种名称, 3 as 数量, '现金' as 付款方式
into tablename
union all select 'aaa', 'a/#$%&,a ', 5, '预付'
union all select 'bbb', 'p022', 4, '预付'
union all select 'ccc', 'p011', 10, '现金'
union all select 'bbb', 'a/#$%&,a ', 10, '现金'select * from tablename
/*
客户名称 品种名称 数量 付款方式
aaa a/#$%&,a 3 现金
aaa a/#$%&,a 5 预付
bbb p022 4 预付
ccc p011 10 现金
bbb a/#$%&,a 10 现金
*/--品种名称不固定
declare @s varchar(8000)
set @s = 'select 客户名称, 付款方式, '
select @s = @s + '[' + 品种名称 + '], '
from (select distinct 品种名称 from tablename) a
set @s = @s + '合计 from (select 付款方式 as 排序字段, 客户名称, 付款方式, '
select @s = @s + '(case 品种名称 when ''' + 品种名称 + ''' then 数量 else 0 end) as [' + 品种名称 + '], '
from (select distinct 品种名称 from tablename) a
set @s = @s + '数量 as 合计 from tablename union all select 付款方式 + ''a'', ''小计'', '''', '
select @s = @s + 'sum(case 品种名称 when ''' + 品种名称 + ''' then 数量 else 0 end), '
from (select distinct 品种名称 from tablename) a
set @s = @s + 'sum(数量) as 合计 from tablename group by 付款方式 ' +
'union all select max(付款方式) + ''b'', ''合计'', '''', '
select @s = @s + 'sum(case 品种名称 when ''' + 品种名称 + ''' then 数量 else 0 end), '
from (select distinct 品种名称 from tablename) a
set @s = @s + 'sum(数量) as 合计 from tablename) a order by 排序字段, 客户名称'
exec(@s)
/*
客户名称 付款方式 p011 p022 a/#$%&,a 合计
aaa 现金 0 0 3 3
bbb 现金 0 0 10 10
ccc 现金 10 0 0 10
小计 10 0 13 23
aaa 预付 0 0 5 5
bbb 预付 0 4 0 4
小计 0 4 5 9
合计 10 4 18 32
*/--品种名称固定
select 客户名称, 付款方式, [a/#$%&,a ], [p022], [p011], 合计
from (
select 付款方式 as 排序字段, 客户名称, 付款方式,
(case 品种名称 when 'a/#$%&,a ' then 数量 else 0 end) as [a/#$%&,a ],
(case 品种名称 when 'p022' then 数量 else 0 end) as [p022],
(case 品种名称 when 'p011' then 数量 else 0 end) as [p011],
数量 as 合计
from tablename
union all
select 付款方式 + 'a', '小计', '',
sum(case 品种名称 when 'a/#$%&,a ' then 数量 else 0 end),
sum(case 品种名称 when 'p022' then 数量 else 0 end),
sum(case 品种名称 when 'p011' then 数量 else 0 end),
sum(数量) as 合计
from tablename
group by 付款方式
union all
select max(付款方式) + 'b', '合计', '',
sum(case 品种名称 when 'a/#$%&,a ' then 数量 else 0 end),
sum(case 品种名称 when 'p022' then 数量 else 0 end),
sum(case 品种名称 when 'p011' then 数量 else 0 end),
sum(数量) as 合计
from tablename) a
order by 排序字段, 客户名称
/*
客户名称 付款方式 a/#$%&,a p022 p011 合计
aaa 现金 3 0 0 3
bbb 现金 10 0 0 10
ccc 现金 0 0 10 10
小计 13 0 10 23
aaa 预付 5 0 0 5
bbb 预付 0 4 0 4
小计 5 4 0 9
合计 18 4 10 32
*/drop table tablename