declare @t table(Photo varchar(20),Item varchar(20),Kind varchar(20))
insert into @t select 'LF-A','ALF-A1','qj'
insert into @t select 'LF-A','ALF-A2','qj'
insert into @t select 'LF-A','BLF-A1','cy'
insert into @t select 'LF-B','ALF-B1','qj'
insert into @t select 'LF-B','BLF-B1','cy'
insert into @t select 'LF-B','BLF-B2','cy'
select
isnull(a.Photo,b.Photo) Photo,qj=a.Item,cy=b.Item
from
(select Photo,Item,(select count(*) from @t where Photo=d.Photo and Kind=d.Kind and Item<=d.Item) num from @t d where d.Kind='qj') a
full outer join
(select Photo,Item,(select count(*) from @t where Photo=d.Photo and Kind=d.Kind and Item<=d.Item) num from @t d where d.Kind='cy') b
on
a.Photo=b.Photo and a.num=b.num
order by Photo/*
Photo qj cy
-------------------- -------------------- --------------------
LF-A ALF-A1 BLF-A1
LF-A ALF-A2 NULL
LF-B NULL BLF-B2
LF-B ALF-B1 BLF-B1
*/
insert into @t select 'LF-A','ALF-A1','qj'
insert into @t select 'LF-A','ALF-A2','qj'
insert into @t select 'LF-A','BLF-A1','cy'
insert into @t select 'LF-B','ALF-B1','qj'
insert into @t select 'LF-B','BLF-B1','cy'
insert into @t select 'LF-B','BLF-B2','cy'
select
isnull(a.Photo,b.Photo) Photo,qj=a.Item,cy=b.Item
from
(select Photo,Item,(select count(*) from @t where Photo=d.Photo and Kind=d.Kind and Item<=d.Item) num from @t d where d.Kind='qj') a
full outer join
(select Photo,Item,(select count(*) from @t where Photo=d.Photo and Kind=d.Kind and Item<=d.Item) num from @t d where d.Kind='cy') b
on
a.Photo=b.Photo and a.num=b.num
order by Photo/*
Photo qj cy
-------------------- -------------------- --------------------
LF-A ALF-A1 BLF-A1
LF-A ALF-A2 NULL
LF-B NULL BLF-B2
LF-B ALF-B1 BLF-B1
*/
解决方案 »
- 请问员工绩效数据库表中有哪些属性?新手求助.....
- 求一存储过程,得到字段的最大值,并自动加1后插入--较难
- 请教SQL中的一个函数
- 最近在学SQL,用一个创建临时表的我看不懂希望大家帮我解释一下
- 求助:这样的SQL语句要怎么写?
- sql 数据版本控制
- 表分区,什么概念,有什么作用,具体如何操作呢?谢谢!
- 有人会用SQLConfigDataSource()这个东东吗?SQLServer2000的联机丛书中有.在线等...
- SQL7是否与WIN ME有冲突呀?我怎么装了几次都是启动时Explorer出错?
- tempdb在缓冲池中占用100GB内存
- 两个服务器数据数据复制怎么实现?
- sql2000数据库修复问题
真厉害!
向你学习!
真厉害,可以结贴了.
在置换后列数不确定的情况下就难搞了(就是有更多的qj或cy)
好像oracle有行列置换的功能(我没用过oracle^_^)以下是我之前的解决办法
create table #t (photo varchar(20),Item varchar(20),Kind varchar(20))
insert into #t select 'LF-A','ALF-A1','qj'
insert into #t select 'LF-A','ALF-A2','qj'
insert into #t select 'LF-A','BLF-A1','cy'
insert into #t select 'LF-B','ALF-B1','qj'
insert into #t select 'LF-B','BLF-B1','cy'
insert into #t select 'LF-B','BLF-B2','cy'select t1.photo,t1.item as qj, a.item as cy
from #t t1 inner join
(select photo,item
from #t
where kind = 'cy'
) a on t1.photo = a.photo
where kind = 'qj'可惜不是填充null,不是楼主想要的
但也差不多了,加点修改
select t1.photo,t1.item as qj, a.item as cy
into #a
from #t t1 inner join
( select photo,item
from #t
where kind = 'cy'
) a on t1.photo = a.photo
where kind = 'qj'
alter table #a add id int identity(1,1)
go
update #a
set qj = null
from #a a
where exists(select * from #a where id<a.id and qj=a.qj
)
update #a
set cy = null
from #a a
where exists(select * from #a where id<a.id and cy=a.cy)
go
alter table #a drop column id
select * from #a