动态列名数据视图实现

来源：互联网发布：数据挖掘的分析方法编辑：程序博客网时间：2024/06/05 14:17

测试环境:

select @@VERSION/*Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul  9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft CorporationDeveloper Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)*/

--以下实现如果任何列的值全部为4时，不显示该列。

use mastergosp_configure 'show advanced options', 1;GORECONFIGURE;
GOsp_configure 'Ad Hoc Distributed Queries', 1; --开启OpenRowset/OpenDatasource的访问GORECONFIGURE;GO --测试use Tempdbgo--> -->  set nocount on;if not object_id(N'T','U') is null    drop table TGocreate table t(F1 int,F2 int,F3 int,F4 int,F5 int,F6 int,F7 int,F8 int,F9 int)insert into t select 4,3,2,4,2,3,4,2,6insert into t select 4,1,3,4,2,4,2,3,5insert into t select 4,1,3,4,2,6,3,4,5insert into t select 4,2,1,4,3,2,4,1,3Goif OBJECT_ID('p1','P') is not nulldrop proc p1goCREATE PROCEDURE p1asset nocount on;declare @s nvarchar(4000)select @s=isnull(@s+',','')+quotename(Name)from syscolumns where ID=object_id('T') and Name not in('Student') order by Colidexec('declare @s nvarchar(4000),@i intset @i=1;with bas(Select * from (select *, ROW_NUMBER()over( order by (select 1)) as row from T) as aUNPIVOT(VALUE FOR Cols IN('+@s+'))AS b) select @s=isnull(@s+'','','' select '')+quotename(Cols)+'' as T''+rtrim(@i),@i=@i+1 from b where not exists(select 1 from b as c  where c.Cols=b.Cols and VALUE=4  having COUNT(c.row)=(select MAX(row) from b))group by Colsexec(@s+'' from T'')') goif OBJECT_ID('v_p1','v') is not nulldrop VIEW v_p1goCREATE VIEW v_p1ASSELECT * FROM OPENROWSET(         'sqloledb',          'Trusted_Connection=yes',--验证方式这里用信任连接，可用SQL Server认证的方式来代替         'SET FMTONLY OFF;--只将元数据返回给客户端。可以用于测试响应的格式，而不必实际执行查询。          EXEC tempdb..p1          '     )goselect * from v_p1/*T1T2T3T4T5T6T73223426132423513263452132413*/

问题网址：点击打开链接