select a.ProValue, b.ProValue, c.ProValue, d.ProValue from
DynamicProperty as a, DynamicProperty as b, DynamicProperty as c, DynamicProperty as d where
a.ProID = 1 and b.ProID = 2 and c.ProID = 3 and d.ProID = 4
union all
select '1','2','3','4'
DynamicProperty as a, DynamicProperty as b, DynamicProperty as c, DynamicProperty as d where
a.ProID = 1 and b.ProID = 2 and c.ProID = 3 and d.ProID = 4
union all
select '1','2','3','4'
已经解决的帖子在哪里?
CREATE TABLE DynamicProperty (
ProID int IDENTITY (1, 1) NOT NULL ,
ProName varchar (256)
)
GOCREATE TABLE UserBook (
UserID int NOT NULL ,
PropID int NOT NULL ,
PropValue varchar (256)
)
GOALTER TABLE UserBook WITH NOCHECK ADD
CONSTRAINT PK_UserBook PRIMARY KEY CLUSTERED (
UserID,
PropID
)
GO聪明的朋友可能一下子知道我要干什么了。一个User可能有好多属性,而且这些属性是动态的,属性的描述表现在DynamicProperty这个表中,而属性的值表现在UserBook中(请注意,这个表是双关键字的)。
而我现在要返回这样的纪录集,每个纪录能够表现单个用户的所有信息(就是相当于有这样的一个视图:UserID(Primary Key), Property1, Property2, Property3...)。
1.你可以用交叉报表,比较简单但不太美观
2.你可以用外部数据源,建一个死的列,然后往里面填数,比较复杂但比较美观
3.你可以用数据存储做数据源,执行速度快而且也比较美观,
在数据存储里面建一临时表,然后用游标取出数据并插入到临时表
最后从临时表中select出来数据后,再删除临时表
4.你可以动态创建数据窗口,根据要生成的列数,也可行
declare @crsql varchar(300)
declare @insql varchar(300)
set @crsql='create table tmp ('
set @insql='insert into tmp values ('''
select @crsql=@crsql+'a'+cast(proid as varchar(5))+' varchar(30),',
@insql=@insql+provalue+''','''
from DynamicProperty
set @crsql=left(@crsql,len(@crsql)-1)+')'
set @insql=left(@insql,len(@insql)-2)+')'
print @crsql
print @insql
exec(@crsql)
exec(@insql)
select * from tmp
set @sql='select distinct a.userid,'
select @sql=@sql+'(select propvalue from userbook where userid=a.userid and propid='+cast(proid as varchar(5))+') as '+proname+','
from DynamicProperty
set @sql=left(@sql,len(@sql)-1)+' from (select distinct userid from userbook) a,DynamicProperty b'
print @sql
exec(@sql)