SQL交叉表实例很简单的一个东西,见网上好多朋友问“怎么实现交叉表?”,以下是我写的一个例子,数据库基于SQL SERVER 2000。-- ====================================================== --交叉表实例 -- ======================================================建表:在查询分析器里运行: CREATE TABLE [Test] ( [id] [int] IDENTITY (1, 1) NOT NULL , [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Source] [numeric](18, 0) NULL ) ON [PRIMARY] GOINSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60) INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70) INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80) INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75) INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57) INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80) INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)Go交叉表语句的实现: --用于:交叉表的列数是确定的(用了这个很不错!) select name,sum(case subject when '数学' then source else 0 end) as '数学', sum(case subject when '英语' then source else 0 end) as '英语', sum(case subject when '语文' then source else 0 end) as '语文' from test group by name --用于:交叉表的列数是不确定的不确定列,字段2的值范围不固定。create table a (id VARchar(4), F2 VARCHAR(2), F3 INT)insert a(ID,F2,F3) select 'A001','01',12 UNION ALL SELECT 'A001','02',24 UNION ALL SELECT 'A002','01',10 UNION ALL SELECT 'A002','03',5 UNION ALL SELECT 'A003','02',6 UNION ALL SELECT 'A004','04',13 --SELECT * FROM A --不确定列数 declare @sql varchar(8000) set @sql = 'select ID,' select @sql = @sql + 'sum(case F2 when ''' +F2+''' then F3 else '''' end) as '''+ '字段('+F2+')'',' from (select distinct F2 from A) as B select @sql = left(@sql,len(@sql)-1) + ' from A group by ID'--PRINT @SQL exec(@sql)go ============================================================================== 交叉表: CREATE TABLE Test(字段1 VARCHAR(10),字段2 VARCHAR(10),字段3 VARCHAR(10)) INSERT Test SELECT 'a001', '01' , 12 UNION ALL SELECT 'a001', '02' , 24 UNION ALL SELECT 'a002', '01' , 10 UNION ALL SELECT 'a002', '03' , 5 UNION ALL SELECT 'a003', '02' , 6 UNION ALL SELECT 'a004', '04' , 13 SELECT * FROM TestSELECT 字段1, [字段(01)]=MAX(CASE WHEN 字段2 ='01' THEN 字段3 ELSE '' END), [字段(02)]=MAX(CASE WHEN 字段2 ='02' THEN 字段3 ELSE '' END), [字段(03)]=MAX(CASE WHEN 字段2 ='03' THEN 字段3 ELSE '' END), [字段(04)]=MAX(CASE WHEN 字段2 ='04' THEN 字段3 ELSE '' END) FROM Test GROUP BY 字段1
普通行列转换假设有张学生成绩表(t)如下Name Subject Result 张三 语文 73 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94想变成 姓名 语文 数学 物理 张三 73 83 93 李四 74 84 94create table #t ( Name varchar(10) , Subject varchar(10) , Result int )insert into #t(Name , Subject , Result) values('张三','语文','73') insert into #t(Name , Subject , Result) values('张三','数学','83') insert into #t(Name , Subject , Result) values('张三','物理','93') insert into #t(Name , Subject , Result) values('李四','语文','74') insert into #t(Name , Subject , Result) values('李四','数学','83') insert into #t(Name , Subject , Result) values('李四','物理','93')declare @sql varchar(8000) set @sql = 'select Name as ' + '姓名' select @sql = @sql + ' , sum(case Subject when ''' + Subject + ''' then Result end) [' + Subject + ']' from (select distinct Subject from #t) as a set @sql = @sql + ' from #t group by name' exec(@sql) drop table #t--结果 姓名 数学 物理 语文 ---------- ----------- ----------- ----------- 李四 83 93 74 张三 83 93 73
---------------------------------------------------- 如果上述两表互相换一下:即姓名 语文 数学 物理 张三 73 83 93 李四 74 84 94想变成 Name Subject Result 张三 语文 73 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94create table #t ( 姓名 varchar(10) , 语文 int , 数学 int , 物理 int )insert into #t(姓名 , 语文 , 数学 , 物理) values('张三',73,83,93) insert into #t(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select 姓名 as Name,'语文' as Subject,语文 as Result from #t union select 姓名 as Name,'数学' as Subject,数学 as Result from #t union select 姓名 as Name,'物理' as Subject,物理 as Result from #t order by 姓名 desc drop table #t
--带符号合并行列转换--有表t,其数据如下: a b 1 1 1 2 1 3 2 1 2 2 3 1 --如何转换成如下结果: a b 1 1,2,3 2 1,2 3 1 create table tb ( a int, b int ) insert into tb(a,b) values(1,1) insert into tb(a,b) values(1,2) insert into tb(a,b) values(1,3) insert into tb(a,b) values(2,1) insert into tb(a,b) values(2,2) insert into tb(a,b) values(3,1) goif object_id('pubs..f_hb') is not null drop function f_hb go--创建一个合并的函数 create function f_hb(@a int) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + ',' + cast(b as varchar) from tb where a = @a set @str = right(@str , len(@str) - 1) return(@str) End go--调用自定义函数得到结果: select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果 a b ----------- ------ 1 1,2,3 2 1,2 3 1(所影响的行数为 3 行) 多个前列的合并 数据的原始状态如下: ID PR CON OP SC 001 p c 差 6 001 p c 好 2 001 p c 一般 4 002 w e 差 8 002 w e 好 7 002 w e 一般 1 =========================== 用SQL语句实现,变成如下的数据 ID PR CON OPS 001 p c 差(6),好(2),一般(4) 002 w e 差(8),好(7),一般(1)if object_id('pubs..tb') is not null drop table tb gocreate table tb ( id varchar(10), pr varchar(10), con varchar(10), op varchar(10), sc int )
insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '差', 6) insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '好', 2) insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '一般', 4) insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '差', 8) insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '好', 7) insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '一般', 1) goif object_id('pubs..test') is not null drop table test go select ID,PR,CON , OPS = op + '(' + cast(sc as varchar(10)) + ')' into test from tb--创建一个合并的函数 if object_id('pubs..f_hb') is not null drop function f_hb go create function f_hb(@id varchar(10),@pr varchar(10),@con varchar(10)) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + ',' + cast(OPS as varchar) from test where id = @id and @pr = pr and @con = con set @str = right(@str , len(@str) - 1) return(@str) End go--调用自定义函数得到结果: select distinct id ,pr , con , dbo.f_hb(id,pr,con) as OPS from testdrop table tb drop table test--结果 id pr con OPS ---------- ---------- ---------- ------------------- 001 p c 差(6),好(2),一般(4) 002 w e 差(8),好(7),一般(1)(所影响的行数为 2 行)create table b (col varchar(20))insert b values ('a') insert b values ('b') insert b values ('c') insert b values ('d') insert b values ('e') declare @sql varchar(1024) set @sql='' select @sql=@sql+b.col+',' from (select col from b) as b set @sql='select '''+@sql+'''' exec(@sql)
--交叉表实例
-- ======================================================建表:在查询分析器里运行:
CREATE TABLE [Test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Source] [numeric](18, 0) NULL
) ON [PRIMARY]
GOINSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)Go交叉表语句的实现:
--用于:交叉表的列数是确定的(用了这个很不错!)
select name,sum(case subject when '数学' then source else 0 end) as '数学',
sum(case subject when '英语' then source else 0 end) as '英语',
sum(case subject when '语文' then source else 0 end) as '语文'
from test
group by name
--用于:交叉表的列数是不确定的不确定列,字段2的值范围不固定。create table a
(id VARchar(4),
F2 VARCHAR(2),
F3 INT)insert a(ID,F2,F3)
select 'A001','01',12
UNION ALL SELECT 'A001','02',24
UNION ALL SELECT 'A002','01',10
UNION ALL SELECT 'A002','03',5
UNION ALL SELECT 'A003','02',6
UNION ALL SELECT 'A004','04',13
--SELECT * FROM A
--不确定列数
declare @sql varchar(8000)
set @sql = 'select ID,'
select @sql = @sql + 'sum(case F2 when ''' +F2+'''
then F3 else '''' end) as '''+ '字段('+F2+')'','
from (select distinct F2 from A) as B
select @sql = left(@sql,len(@sql)-1) + ' from A group by ID'--PRINT @SQL
exec(@sql)go
==============================================================================
交叉表:
CREATE TABLE Test(字段1 VARCHAR(10),字段2 VARCHAR(10),字段3 VARCHAR(10))
INSERT Test SELECT 'a001', '01' , 12
UNION ALL SELECT 'a001', '02' , 24
UNION ALL SELECT 'a002', '01' , 10
UNION ALL SELECT 'a002', '03' , 5
UNION ALL SELECT 'a003', '02' , 6
UNION ALL SELECT 'a004', '04' , 13
SELECT * FROM TestSELECT 字段1,
[字段(01)]=MAX(CASE WHEN 字段2 ='01' THEN 字段3 ELSE '' END),
[字段(02)]=MAX(CASE WHEN 字段2 ='02' THEN 字段3 ELSE '' END),
[字段(03)]=MAX(CASE WHEN 字段2 ='03' THEN 字段3 ELSE '' END),
[字段(04)]=MAX(CASE WHEN 字段2 ='04' THEN 字段3 ELSE '' END)
FROM Test
GROUP BY 字段1
张三 语文 73
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94想变成
姓名 语文 数学 物理
张三 73 83 93
李四 74 84 94create table #t
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)insert into #t(Name , Subject , Result) values('张三','语文','73')
insert into #t(Name , Subject , Result) values('张三','数学','83')
insert into #t(Name , Subject , Result) values('张三','物理','93')
insert into #t(Name , Subject , Result) values('李四','语文','74')
insert into #t(Name , Subject , Result) values('李四','数学','83')
insert into #t(Name , Subject , Result) values('李四','物理','93')declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , sum(case Subject when ''' + Subject + ''' then Result end) [' + Subject + ']'
from (select distinct Subject from #t) as a
set @sql = @sql + ' from #t group by name'
exec(@sql) drop table #t--结果
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 83 93 74
张三 83 93 73
----------------------------------------------------
如果上述两表互相换一下:即姓名 语文 数学 物理
张三 73 83 93
李四 74 84 94想变成
Name Subject Result
张三 语文 73
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94create table #t
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)insert into #t(姓名 , 语文 , 数学 , 物理) values('张三',73,83,93)
insert into #t(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select 姓名 as Name,'语文' as Subject,语文 as Result from #t union
select 姓名 as Name,'数学' as Subject,数学 as Result from #t union
select 姓名 as Name,'物理' as Subject,物理 as Result from #t
order by 姓名 desc drop table #t
--结果
Name Subject Result
---------- ------- -----------
张三 数学 83
张三 物理 93
张三 语文 73
李四 数学 84
李四 物理 94
李四 语文 74(所影响的行数为 6 行)
a b
1 1
1 2
1 3
2 1
2 2
3 1
--如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1 create table tb
(
a int,
b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
goif object_id('pubs..f_hb') is not null
drop function f_hb
go--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(b as varchar) from tb where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果
a b
----------- ------
1 1,2,3
2 1,2
3 1(所影响的行数为 3 行)
多个前列的合并
数据的原始状态如下:
ID PR CON OP SC
001 p c 差 6
001 p c 好 2
001 p c 一般 4
002 w e 差 8
002 w e 好 7
002 w e 一般 1
===========================
用SQL语句实现,变成如下的数据
ID PR CON OPS
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)if object_id('pubs..tb') is not null
drop table tb
gocreate table tb
(
id varchar(10),
pr varchar(10),
con varchar(10),
op varchar(10),
sc int
)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '差', 6)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '好', 2)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '一般', 4)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '差', 8)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '好', 7)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '一般', 1)
goif object_id('pubs..test') is not null
drop table test
go
select ID,PR,CON , OPS = op + '(' + cast(sc as varchar(10)) + ')' into test from tb--创建一个合并的函数
if object_id('pubs..f_hb') is not null
drop function f_hb
go
create function f_hb(@id varchar(10),@pr varchar(10),@con varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(OPS as varchar) from test where id = @id and @pr = pr and @con = con
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct id ,pr , con , dbo.f_hb(id,pr,con) as OPS from testdrop table tb
drop table test--结果
id pr con OPS
---------- ---------- ---------- -------------------
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)(所影响的行数为 2 行)create table b
(col varchar(20))insert b values ('a')
insert b values ('b')
insert b values ('c')
insert b values ('d')
insert b values ('e')
declare @sql varchar(1024)
set @sql=''
select @sql=@sql+b.col+',' from (select col from b) as b
set @sql='select '''+@sql+''''
exec(@sql)