----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-06-10 01:49:18
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Subject:把动态SQL结果集转化为视图查询的方法（运行环境：SQL2005以上版本）
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Range] [int],[Level] [int],[Persons] [nvarchar](10))
INSERT INTO [tb]
SELECT '1','1','a' UNION ALL
SELECT '2','1','b' UNION ALL
SELECT '1','2','c' UNION ALL
SELECT '2','3','d' UNION ALL
SELECT '1','4','e' UNION ALL
SELECT '3','5','f'
--SELECT * FROM [tb]
-->SQL查询如下:
IF OBJECT_ID('p_test') IS NOT NULL
DROP PROC p_test
GO
CREATE PROC p_test
AS
SET NOCOUNT ON
DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME([Level]),
@s1=ISNULL(@s1+',','')+'ISNULL('+QUOTENAME([Level])+','''') Level'+LTRIM([Level])
FROM tb
GROUP BY [Level]
EXEC('
SELECT [Range],'+@s1+'
FROM tb
PIVOT(MAX(Persons) FOR [Level] IN('+@s+'))b
')
SET NOCOUNT OFF
GO
--创建视图：
IF OBJECT_ID('v_test') IS NOT NULL
DROP VIEW v_test
GO
CREATE VIEW v_test
AS
SELECT *
FROM OPENROWSET(
'sqloledb',
'Trusted_Connection=yes', --此处可用'uid=sa;pwd=123' （SQL认证的方式来代替）
'SET FMTONLY OFF; --注意：要加上此选项
EXEC mydb..p_test --这里的mydb要改为实际的数据库名,p_test为创建好的存储过程名
'
)
GO
--查询：
SELECT * FROM v_test;
/*
Range Level1 Level2 Level3 Level4 Level5
----------- ---------- ---------- ---------- ---------- ----------
1 a c e
2 b d
3 f
(3 行受影响)
*/
需求贴：http://topic.csdn.net/u/20100609/16/01aa33e2-d2db-4cee-a514-1dbefb7bc0aa.html?seed=1476244871
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/06/10/5659935.aspx