一个比较有难度的行转列的问题。都来看看吧！！！！！！！！(非常急)
有两张表：jobs和company
jobs
uid ftitle
2程序员
6硬件维护
7软件开发
2网站策划
2人事主管
company
uid fname
2公司1
6公司2
7公司3
我现在要把两个表合并为一个表，表的结构如下
uid fname fitle1 ftitle2
2 公司1 程序员 网站策划
6 公司2 硬件维护 null
7 公司3 软件开发 null
注意：职位表里面只取出与公司表UID对应的前两条记录把它们转换成公司表的列ftitle1和ftitle2其他的省略。
数据是动态的哈。只做演示用。
[解决办法]
如果只要两个
select uid,fname
,(select top 1 ftitle from jobs where uid=a.uid order by ftitle asc) as ftitle1
,(select top 1 ftitle from jobs where uid=a.uid order by ftitle desc) as ftitle2
from company a
[解决办法]
or
临时表,保持次序
select IDENTITY(int,1,1) as id,* into # from jobs
select uid,fname
,(select ftitle from # t where uid=a.uid and not exists (
select 1 from # where uid=a.uid and id <t.id
)
) as ftitle1
,(select ftitle from jobs where uid=a.uid and (
select count(*) from # where uid=a.uid and id <t.id
)=1
) as ftitle2
from company a
[解决办法]
--创建测试环境
drop table jobs,company
go
create table jobs(id int identity(1,1),uid int,ftitle varchar(20))
insert into jobs(uid,ftitle)
select 2, '程序员 '
union all select 6, '硬件维护 '
union all select 7, '软件开发 '
union all select 2, '网站策划 '
union all select 2, '人事主管 '
create table company(uid int,fname varchar(20))
insert into company
select 2, '公司1 '
union all select 6, '公司2 '
union all select 7, '公司3 '
--查询
select a.uid,
a.fname,
id1=(select top 1 id from jobs b where b.uid=a.uid order by b.ftitle),
fitle1=(select top 1 ftitle from jobs b where b.uid=a.uid order by b.ftitle),
id2=(select top 1 id from jobs c where c.uid=a.uid and c.ftitle <> (select top 1 ftitle from jobs b where b.uid=a.uid order by b.ftitle)),
fitle2=(select top 1 ftitle from jobs c where c.uid=a.uid and c.ftitle <> (select top 1 ftitle from jobs b where b.uid=a.uid order by b.ftitle))
from company a
/*
uid fname id1 fitle1 id2 fitle2
----------- -------------------- ----------- -------------------- ----------- --------------------
2 公司1 1 程序员 4 网站策划
6 公司2 2 硬件维护 NULL NULL
7 公司3 3 软件开发 NULL NULL
（所影响的行数为 3 行）
*/
[解决办法]
select a.uid,
a.fname,
ftitle1=(select top 1 ftitle from jobs b where b.uid=a.uid order by b.ftitle),
ftitle2=(select top 1 ftitle from jobs c where c.uid=a.uid and c.ftitle not in(select top 1 ftitle from jobs b where b.uid=a.uid order by b.ftitle)order by c.ftitle),
ftitle3= (select top 1 ftitle from jobs d where d.uid=a.uid and d.ftitle not in(select top 2 ftitle from jobs b where b.uid=a.uid order by b.ftitle)order by d.ftitle)
from company a
查询结果
uid fname ftitle1 ftitle2 ftitle3
2公司1程序员 人事主管 网站策划
6公司2硬件维护 NULL NULL
7公司3软件开发 NULL NULL
按中文升序排的，你可以更改排序顺序来去掉你不需要的
如果需要你给的这个结果，可以写成
select a.uid,
a.fname,
ftitle1=(select top 1 ftitle from jobs b where b.uid=a.uid order by b.ftitle),
ftitle2=(select top 1 ftitle from jobs c where c.uid=a.uid and c.ftitle not in(select top 1 ftitle from jobs b where b.uid=a.uid order by b.ftitle)order by c.ftitle desc)
from company a
查询结果
uid fname fitle1 ftitle2
2 公司1 程序员 网站策划
6 公司2 硬件维护 null
7 公司3 软件开发 null
[解决办法]
---用动态语句实现的话，需增加辅助列
---借楼上的楼上的数据
--创建测试环境
if object_id( 'jobs ') is not null
drop table jobs
go
if object_id( 'company ') is not null
drop table company
go
create table jobs(uid int,ftitle varchar(20))
insert into jobs
select 2, '程序员 '
union all select 6, '硬件维护 '
union all select 7, '软件开发 '
union all select 2, '网站策划 '
union all select 2, '人事主管 '
create table company(uid int,fname varchar(20))
insert into company
select 2, '公司1 '
union all select 6, '公司2 '
union all select 7, '公司3 '
go
--增加id辅助列
alter table jobs add id int
go
declare @id int
set @id=0
update jobs set id=@id,@id=@id+1
go
update a set
id=(select count(1) from jobs where uid=a.uid and id <=a.id)
from jobs a
--查看增加辅助列后的测试数据
select * from jobs
select * from company
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',max(case id when ' ' '+cast(id as varchar)+ ' ' ' then ftitle else null end)
as [ftitle '+cast(id as varchar)+ '] ' from jobs group by id
exec( 'select a.uid,a.fname '+@sql+ 'from jobs b inner join company a on a.uid=b.uid group by a.uid,a.fname ')
