我行列转换的代码如下:
declare @sql varchar(8000)
set @sql = 'select ID '
select @sql = @sql + ' , sum(case KIND when ''' + cast(KIND as varchar) + ''' then NUM else 0 end) [' + cast(KIND as varchar) + ']'
from (select distinct KIND from tb) as a
set @sql = @sql + ' from tb group by ID '
if object_id('aa') is not null
drop table aaexec('select * into aa from('+@sql+')a');
select * from aa
union all
select '总计:',sum(a.k536),sum(a.k537),sum(a.k538),sum(a.k539),sum(a.k541)
from (
select * from aa
)a
给大家做个参考吧大家不要贴网上的合并项的代码了,我看过了,跟我要的不是一样的啊,我基础差,你们贴了我一下子也看不懂的。摆脱大家了!
declare @sql varchar(8000)
set @sql = 'select ID '
select @sql = @sql + ' , sum(case KIND when ''' + cast(KIND as varchar) + ''' then NUM else 0 end) [' + cast(KIND as varchar) + ']'
from (select distinct KIND from tb) as a
set @sql = @sql + ' from tb group by ID '
if object_id('aa') is not null
drop table aaexec('select * into aa from('+@sql+')a');
select * from aa
union all
select '总计:',sum(a.k536),sum(a.k537),sum(a.k538),sum(a.k539),sum(a.k541)
from (
select * from aa
)a
给大家做个参考吧大家不要贴网上的合并项的代码了,我看过了,跟我要的不是一样的啊,我基础差,你们贴了我一下子也看不懂的。摆脱大家了!
go
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
CREATE TABLE tb(ID VARCHAR(10),NAME VARCHAR(50),KIND VARCHAR(10),NUM INT)
INSERT INTO tb
SELECT '1','TOM','K536',1 UNION ALL
SELECT '1','JIM','K537',1 UNION ALL
SELECT '1','JIM','K538',1 UNION ALL
SELECT '1','JICK','K541',1 UNION ALL
SELECT '2','HELLO','K541',1 UNION ALL
SELECT '971','NAME1','K539',1 UNION ALL
SELECT '971','NAME1','K538',1 UNION ALL
SELECT '971','NAME1','K539',2 UNION ALL
SELECT '971','NAME2','K541',1 UNION ALL
SELECT '971','NAME2','K541',2 UNION ALL
SELECT '971','NAME2','K541',1 UNION ALL
SELECT '971','NAME3','K541',1 UNION ALL
SELECT '971','HELLO','K541',1 godeclare @s nvarchar(4000)
set @s=N'select [ID]=isnull(ID,N''總計'')'
select @s=@s+','+quotename(KIND)+'=sum(case KIND when '+quotename(KIND,'''')+' then NUM else 0 end)'
from tb group by KIND
exec(@s+N' from tb group by ID with rollup')ID K536 K537 K538 K539 K541
---------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 0 1
2 0 0 0 0 1
971 0 0 1 3 6
總計 1 1 2 3 8(4 個資料列受到影響)
你得出的结果是我用正常的行列转换得到的,不是合并NAME之后的结果啊!
INSERT INTO tb
SELECT '1','TOM','K536',1 UNION ALL
SELECT '1','JIM','K537',1 UNION ALL
SELECT '1','JIM','K538',1 UNION ALL
SELECT '1','JICK','K541',1 UNION ALL
SELECT '2','HELLO','K541',1 UNION ALL
SELECT '971','NAME1','K539',1 UNION ALL
SELECT '971','NAME1','K538',1 UNION ALL
SELECT '971','NAME1','K539',2 UNION ALL
SELECT '971','NAME2','K541',1 UNION ALL
SELECT '971','NAME2','K541',2 UNION ALL
SELECT '971','NAME2','K541',1 UNION ALL
SELECT '971','NAME3','K541',1 UNION ALL
SELECT '971','HELLO','K541',1 declare @sql varchar(8000)
set @sql = 'select isnull(id,''合计'') id '
select @sql = @sql + ' , sum(case KIND when ''' + KIND + ''' then 1 else 0 end) [' + KIND + ']'
from (select distinct KIND from (select distinct ID,NAME,KIND from tb) t) as a
set @sql = @sql + ' from (select distinct ID,NAME,KIND from tb) t group by id with rollup'
exec(@sql) drop table tb/*
id K536 K537 K538 K539 K541
---------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 0 1
2 0 0 0 0 1
971 0 0 1 1 3
合计 1 1 2 1 5
*/
use Tempdb
go
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
CREATE TABLE tb(ID VARCHAR(10),NAME VARCHAR(50),KIND VARCHAR(10),NUM INT)
INSERT INTO tb
SELECT '1' as id,'TOM' as name,'K536' as kind,1 as num UNION ALL
SELECT '1','JIM','K537',1 UNION ALL
SELECT '1','JIM','K538',1 UNION ALL
SELECT '1','JICK','K541',1 UNION ALL
SELECT '2','HELLO','K541',1 UNION ALL
SELECT '971','NAME1','K539',1 UNION ALL
SELECT '971','NAME1','K538',1 UNION ALL
SELECT '971','NAME1','K539',2 UNION ALL
SELECT '971','NAME2','K541',1 UNION ALL
SELECT '971','NAME2','K541',2 UNION ALL
SELECT '971','NAME2','K541',1 UNION ALL
SELECT '971','NAME3','K541',1 UNION ALL
SELECT '971','HELLO','K541',1
--分区部分
IF OBJECT_ID('tb_hb') IS NOT NULL DROP TABLE tb_hb
CREATE TABLE tb_hb(ID VARCHAR(10),KIND VARCHAR(10), knum int)
insert into tb_hb
select a.id,a.kind, 1 as knum
from tb a group by a.id,a.kind,a.namedeclare @sql varchar(8000)
set @sql = 'select ID '
select @sql = @sql + ' , sum(case KIND when ''' + cast(KIND as varchar) + ''' then knum else 0 end) [' + cast(KIND as varchar)
+ ']'
from (select distinct KIND from tb) as a
set @sql = @sql + ' from tb_hb group by ID '
--print @sql;
if object_id('aa') is not null
drop table aa
exec('select * into aa from('+@sql+')a'); --总计部分
set @sql = 'select ID '
select @sql = @sql + ' , sum(case KIND when ''' + cast(KIND as varchar) + ''' then knum else 0 end) [' + cast(KIND as varchar)
+ ']'
from (select distinct KIND from tb) as a
set @sql = @sql + ' from tb_zj group by ID '
print @sql;
if object_id('bb') is not null
drop table bb
exec('select * into bb from('+@sql+')a'); select * from aa
union all
select * from bb