declare @sql varchar(4000)
set @sql = 'select ProjectID '
select @sql = @sql + ',max(case PropertyID when ' ' '+PropertyID+ ' ' ' then Value end) [ '+PropertyID+ '] '
from (select distinct PropertyID from ProjectProperty) as a
select @sql = @sql+ ' from ProjectProperty group by ProjectID '
exec(@sql) 个有哪位高人,将上面行列转换的存储过程 转化成视图(或者可以在视图中编写sql)
set @sql = 'select ProjectID '
select @sql = @sql + ',max(case PropertyID when ' ' '+PropertyID+ ' ' ' then Value end) [ '+PropertyID+ '] '
from (select distinct PropertyID from ProjectProperty) as a
select @sql = @sql+ ' from ProjectProperty group by ProjectID '
exec(@sql) 个有哪位高人,将上面行列转换的存储过程 转化成视图(或者可以在视图中编写sql)
ASSELECT ProjectID,
MAX(case PropertyID when 1 THEN [VALUE] END),
MAX(case PropertyID when 2 THEN [VALUE] END),
MAX(case PropertyID when 3 THEN [VALUE] END),
MAX(case PropertyID when 4 THEN [VALUE] END),
MAX(case PropertyID when 5 THEN [VALUE] END)
--根据PropertyID数量,以此类推。不重复的PropertyID越多,添加的语句越多
FROM ProjectProperty group by ProjectIDGO
CREATE PROC myProc
AS
BEGIN
declare @sql varchar(4000)
set @sql = 'select ProjectID'
select @sql = @sql + ',max(case PropertyID when '''+PropertyID+ ''' then Value end) ['+PropertyID+ ']'
from (select distinct PropertyID from ProjectProperty) as a
select @sql = @sql + ' from ProjectProperty group by ProjectID '
exec(@sql)
ENDGOCREATE VIEW myView
AS
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=密码',
'EXEC 库名.dbo.myProc') AS a
GO
SELECT * FROM myViewGODROP VIEW myViewa
DROP PROC myProc