交叉表实例

--CREATE TABLE Test ( id int  IDENTITY (1,1),names nvarchar (50),subject nvarchar (50),Source decimal(18, 0) )
INSERT INTO [test] ([names],[subject],[Source]) values (N'张三',N'语文',60)
INSERT INTO [test] ([names],[subject],[Source]) values (N'李四',N'数学',70)
INSERT INTO [test] ([names],[subject],[Source]) values (N'王五',N'英语',80)
INSERT INTO [test] ([names],[subject],[Source]) values (N'王五',N'数学',75)
INSERT INTO [test] ([names],[subject],[Source]) values (N'王五',N'语文',57)
INSERT INTO [test] ([names],[subject],[Source]) values (N'李四',N'语文',80)
INSERT INTO [test] ([names],[subject],[Source]) values (N'张三',N'英语',100)
select * from test;
select names,

--动态交叉表

declare @sql varchar(8000)
set @sql = 'select names,'
select @sql = @sql + 'sum(case subject when '''+subject+''' then source else null end) as '''+subject+''','
from (select distinct subject from test) as a
print @sql;
select @sql = left(@sql,len(@sql)-1) + 'from test group by names'
print @sql;
exec(@sql)

 

 

Create Table 表1(组名 varchar(10),成员1id varchar(10),成员2id varchar(10),成员3id varchar(10))
--插入数据
insert into 表1
select '冲锋组','1','2','3' union
select '后卫组','2','3','4'
select * from 表1
Create Table 表2(成员id varchar(10),成员姓名 varchar(10))
--插入数据
insert into 表2
select '1','张三' union
select '2','李四' union
select '3','王五' union
select '4','陆二'

--测试语句
select a.组名,
成员1=(select 成员姓名 from 表2 b where a.成员1id=b.成员id),
成员1=(select 成员姓名 from 表2 b where a.成员2id=b.成员id),
成员1=(select 成员姓名 from 表2 b where a.成员3id=b.成员id)
from 表1 a

select 表1.组名, (select 表1.成员姓名 from 表2 b where 表1.成员1id=表2.成员id) as 成员1id,
(select 表1.成员姓名 from 表2 b where 表1.成员2id=表2.成员id) as 成员2id,
(select 表1.成员姓名 from 表2 b where 表1.成员3id=表2.成员id) as 成员3id
from 表1,表2
create table tt
(
 ID int primary key  identity(1,1),
week varchar(50),
Sday varchar(50),
People varchar(50)
)

insert into tt values('周一','上午','李四')
insert into tt values('周一','下午','gg')
insert into tt values('周二','上午','33')
insert into tt values('周二','下午','55')
select *from tt;
select a.Week as 星期, 上午 = Max(case  when a.Sday ='上午' then a.People else null end ),下午= Max(case when a.Sday='下午' then a.People else null end )from tt a   group by a.Week

 

create table  score (
 姓名 varchar(50) ,
课程 varchar(50),
分数 int 
)

insert into score values('张三','语文',74) 

 insert into score values('张三','数学',83) 

insert into score values('张三','物理',93) 

 insert into score values('李四','语文',74) 

 insert into score values('李四','数学',84) 

 insert into score values('李四','物理',94) 

select * from score;

select * from score pivot(Max(分数) for 课程 in (语文，数学，物理))a;