27,579
社区成员
发帖
与我相关
我的任务
分享
select FID, 名称=max(case FileName when '名称' then name else null end),
大小=max(case FileName when '大小' then name else null end),
时间=max(case FileName when '时间' then name else null end)
from
(
select b.Name as FileName,a.Name,a.FID
from FileValue a join FileName b on a.docId=b.docID
) a
group by FID
FID 名称 大小 时间
d1 文件1 1kb 2009
d2 文件2 2kb 2008
---测试数据---
if object_id('[FileName]') is not null drop table [FileName]
go
create table [FileName]([docID] int,[Name] varchar(4),[FileId] varchar(2))
insert [FileName]
select 1,'名称','te' union all
select 2,'大小','te' union all
select 3,'时间','te'
go
if object_id('[FileValue]') is not null drop table [FileValue]
go
create table [FileValue]([docId] int,[Name] varchar(5),[FileId] varchar(2),[FID] varchar(2))
insert [FileValue]
select 1,'文件1','te','d1' union all
select 2,'1kb','te','d1' union all
select 3,'2009','te','d1' union all
select 1,'文件2','te','d2' union all
select 2,'2kb','te','d2' union all
select 3,'2008','te','d2'
---查询---
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'max(case when a.name='''+name+''' then b.name else '''' end) as '+name
from
(select distinct a.name from [FileName] a,[FileValue] b where a.docID=b.docID) t
set @sql='select b.FID as 文件ID,'
+@sql
+' from [FileName] a,[FileValue] b where a.docID=b.docID group by b.FID'
--print @sql
exec (@sql)
---结果---
文件ID 大小 名称 时间
---- ----- ----- -----
d1 1kb 文件1 2009
d2 2kb 文件2 2008
(2 行受影响)