数据库行列交换实例

来源：互联网发布：西安java 编辑：程序博客网时间：2024/06/18 10:11

请写SQL语句将下表test
person ttime Iotype
001 8:00 1
001 12:00 2
001 13:00 1
001 17:00 2
002 8:00 1
002 12:00 2

转换成下表
wpid person in out
1 001 8:00 12:00
2 001 13:00 17:00
3 001 8:00 12:00

普通行列转换

(爱新觉罗.毓华 2007-11-18于海南三亚)

假设有张学生成绩表(tb)如下:

Name Subject Result

张三语文　　74

张三数学　　83

张三物理　　93

李四语文　　74

李四数学　　84

李四物理　　94

-------------------------------------------------------------------------

想变成

姓名语文数学物理

---------- ----------- ----------- -----------

李四 74 84 94

张三 74 83 93

create table tb

(

Name varchar(10) ,

Subject varchar(10) ,

Result int

)

insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)

insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)

insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)

insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)

insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)

insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)

--静态SQL,指subject只有语文、数学、物理这三门课程。

select name 姓名,

max(case subject when '语文' then result else 0 end) 语文,

max(case subject when '数学' then result else 0 end) 数学,

max(case subject when '物理' then result else 0 end) 物理

from tb

group by name

姓名语文数学物理

---------- ----------- ----------- -----------

李四 74 84 94

张三 74 83 93

--动态SQL,指subject不止语文、数学、物理这三门课程。

declare @sql varchar(8000)

set @sql = 'select Name as ' + '姓名'

select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'

from (select distinct Subject from tb) as a

set @sql = @sql + ' from tb group by name'

exec(@sql)

姓名数学物理语文

---------- ----------- ----------- -----------

李四 84 94 74

张三 83 93 74

-------------------------------------------------------------------

/*加个平均分，总分

姓名语文数学物理平均分总分

---------- ----------- ----------- ----------- -------------------- -----------

李四 74 84 94 84.00 252

张三 74 83 93 83.33 250

--静态SQL,指subject只有语文、数学、物理这三门课程。

select name 姓名,

max(case subject when '语文' then result else 0 end) 语文,

max(case subject when '数学' then result else 0 end) 数学,

max(case subject when '物理' then result else 0 end) 物理,

cast(avg(result*1.0) as decimal(18,2)) 平均分,

sum(result) 总分

from tb

group by name

姓名语文数学物理平均分总分

---------- ----------- ----------- ----------- -------------------- -----------

李四 74 84 94 84.00 252

张三 74 83 93 83.33 250

--动态SQL,指subject不止语文、数学、物理这三门课程。

declare @sql1 varchar(8000)

set @sql1 = 'select Name as ' + '姓名'

select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'

from (select distinct Subject from tb) as a

set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'

exec(@sql1)

姓名数学物理语文平均分总分

---------- ----------- ----------- ----------- -------------------- -----------

李四 84 94 74 84.00 252

张三 83 93 74 83.33 250

drop table tb

---------------------------------------------------------

如果上述两表互相换一下：即

姓名语文数学物理

张三 74　　83　　93

李四 74　　84　　94

想变成

Name Subject Result

---------- ------- -----------

李四语文 74

李四数学 84

李四物理 94

张三语文 74

张三数学 83

张三物理 93

create table tb1

(

姓名 varchar(10) ,

语文 int ,

数学 int ,

物理 int

)

insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)

insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)

select * from

(

select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1

union all

select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1

union all

select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1

) t

order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end

--------------------------------------------------------------------

/*加个平均分,总分

Name Subject Result

---------- ------- --------------------

李四语文 74.00

李四数学 84.00

李四物理 94.00

李四平均分 84.00

李四总分 252.00

张三语文 74.00

张三数学 83.00

张三物理 93.00

张三平均分 83.33

张三总分 250.00

select * from

(

select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1

union all

select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1

union all

select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1

union all

select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1

union all

select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1

) t

order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end

drop table tb1

create table T(person varchar(10), ttime varchar(10), Iotype int)

insert into T values('001', '08:00', 1 )

insert into T values('001', '12:00', 2 )

insert into T values('001', '13:00', 1 )

insert into T values('001', '17:00', 2 )

insert into T values('002', '08:00', 1 )

insert into T values('002', '12:00', 2 )

--2000

select

person,

[in]=max(case when Iotype=1 then ttime else '' end),

[out]=max(case when Iotype=2 then ttime else '' end)

from

(select

con=(select count(1) from T where person=a.person and Iotype=a.Iotype and ttime<=a.ttime)

from

T a)Tmp

group by person,con

order by person

--2005

select

person,

[in]=max(case when Iotype=1 then ttime else '' end),

[out]=max(case when Iotype=2 then ttime else '' end)

from

(select

con=row_number()over(partition by person,Iotype order by ttime)--这一段变化

from

T a)Tmp

group by person,con

order by person

person in out

---------- ---------- ----------

001 08:00 12:00

001 13:00 17:00

002 08:00 12:00

（所影响的行数为 3 行）

--加个序号

--2000

select

[wpid]=(select count(1) from T where (person<t1.person or (person=t1.person and ttime<t1.[in])) and Iotype=1 )+1,

from

(select

person,

[in]=max(case when Iotype=1 then ttime else '' end),

[out]=max(case when Iotype=2 then ttime else '' end)

from

(select

con=(select count(1) from T where person=a.person and Iotype=a.Iotype and ttime<=a.ttime)

from

T a)Tmp

group by person,con)T1

order by [wpid]

--2005

select

[wpid]=dense_rank()over(order by person),

person,

[in]=max(case when Iotype=1 then ttime else '' end),

[out]=max(case when Iotype=2 then ttime else '' end)

from

(select

con=row_number()over(partition by person,Iotype order by ttime)--这一段变化

from

T a)Tmp

group by person,con

order by person

wpid person in out

----------- ---------- ---------- ----------

1 001 08:00 12:00

2 001 13:00 17:00

3 002 08:00 12:00

（所影响的行数为 3 行）

create table tb(person varchar(10), ttime varchar(10), Iotype int)

insert into tb values('001', '08:00', 1 )

insert into tb values('001', '12:00', 2 )

insert into tb values('001', '13:00', 1 )

insert into tb values('001', '17:00', 2 )

insert into tb values('002', '08:00', 1 )

insert into tb values('002', '12:00', 2 )

select person ,

max(case Iotype when 1 then ttime else '' end) [in],

max(case Iotype when 2 then ttime else '' end) [out]

from

(

select px = case when ttime <= '12:00' then 1 else 2 end , * from tb

) t

group by person , px

order by person , px

drop table tb

person in out

---------- ---------- ----------

001 08:00 12:00

001 13:00 17:00

002 08:00 12:00

（所影响的行数为 3 行）

create table test (person char(3), ttime datetime, Iotype tinyint)

insert into test

select '001', '8:00',1 union

select '001', '12:00',2 union

select '001', '13:00',1 union

select '001', '17:00',2 union

select '002', '8:00',1 union

select '002', '12:00',2

-- 一种比较快捷的算法

declare @i table (id int identity(1,1), person char(3), ttime datetime)

declare @o table (id int identity(1,1), person char(3), ttime datetime)

insert into @i select person,ttime from test where Iotype=1 order by person, ttime

insert into @o select person,ttime from test where Iotype=2 order by person, ttime

select wpid=i.id, i.person, [in]=convert(varchar(5),i.ttime,108), [out]=convert(varchar(5),o.ttime,108)

from @i i, @o o where i.id=o.id

-- wpid,person,in,out

-- 1,001,08:00,12:00

-- 2,001,13:00,17:00

-- 3,002,08:00,12:00

-- 一种比较严格的算法

declare @i table (id int identity(1,1), person char(3), ttime datetime)

insert into @i select person,ttime from test where Iotype=1 order by person, ttime

select wpid=i.id, i.person, [in]=i.ttime

,[out] = (select min(ttime) from test where Iotype=2 and person=i.person and ttime>i.ttime )

from @i i

drop table test

－－这个问题不完全是行列转换，是个记录匹配问题

－－q: 是否每个Iotype=1的记录必然有Iotype=2的记录相匹配?

－－a(假设): 是的，且每个Iotype=1的记录必然在上一个Iotype=2的记录之后：

create table test (person char(3), ttime datetime, Iotype tinyint)

insert into test

select '001', '8:00',1 union

select '001', '12:00',2 union

select '001', '13:00',1 union

select '001', '17:00',2 union

select '002', '8:00',1 union

select '002', '12:00',2

declare @i table (id int identity(1,1), person char(3), ttime datetime)

declare @o table (id int identity(1,1), person char(3), ttime datetime)

insert into @i select person,ttime from test where Iotype=1 order by person, ttime

insert into @o select person,ttime from test where Iotype=2 order by person, ttime

select wpid=i.id, i.person, [in]=convert(varchar(5),i.ttime,108), [out]=convert(varchar(5),o.ttime,108)

from @i i, @o o where i.id=o.id

-- wpid,person,in,out

-- 1,001,08:00,12:00

-- 2,001,13:00,17:00

-- 3,002,08:00,12:00

drop table test

create table ta(person varchar(3),ttime varchar(10),Iotype int)

insert ta select '001','08:00',1

insert ta select '001','12:00',2

insert ta select '001','13:00',1

insert ta select '001','17:00',2

insert ta select '002','08:00',1

insert ta select '002','12:00',2

declare @s varchar(8000)

set @s = 'select person'

select @s = @s + ',['+ltrim(iotype) +']=max(case when iotype = '+ltrim(iotype) + ' then ttime else null end)'

from (select distinct iotype from ta ) a

exec (@s+ ' from (select person,ttime,iotype,p = case when ttime > ''12:59'' then 1 else 0 end from ta ) a group by person,p order by person')

drop table ta

person 1 2

------ ---------- ----------

001 08:00 12:00

001 13:00 17:00

002 08:00 12:00

警告: 聚合或其它 SET 操作消除了空值。