测试环境:
select @@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 6.1 <X86> (Build 7601: Service Pack 1)
*/
--以下实现如果任何列的值全部为4时,不显示该列。
use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1; --开启OpenRowset/OpenDatasource的访问
GO
RECONFIGURE;
GO
--测试
use Tempdb
go
--> -->
set nocount on;
if not object_id(N'T','U') is null
drop table T
Go
create 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,6
insert into t select 4,1,3,4,2,4,2,3,5
insert into t select 4,1,3,4,2,6,3,4,5
insert into t select 4,2,1,4,3,2,4,1,3
Go
if OBJECT_ID('p1','P') is not null
drop proc p1
go
CREATE PROCEDURE p1
as
set 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 Colid
exec('declare @s nvarchar(4000),@i int
set @i=1
;with b
as
(
Select
*
from (select *, ROW_NUMBER()over( order by (select 1)) as row from T) as a
UNPIVOT
(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 Cols
exec(@s+'' from T'')
')
go
if OBJECT_ID('v_p1','v') is not null
drop VIEW v_p1
go
CREATE VIEW v_p1
AS
SELECT *
FROM OPENROWSET(
'sqloledb',
'Trusted_Connection=yes', --验证方式这里用信任连接,可用SQL Server认证的方式来代替
'SET FMTONLY OFF; --只将元数据返回给客户端。可以用于测试响应的格式,而不必实际执行查询。
EXEC tempdb..p1
'
)
go
select * from v_p1
/*
T1 T2 T3 T4 T5 T6 T7
3 2 2 3 4 2 6
1 3 2 4 2 3 5
1 3 2 6 3 4 5
2 1 3 2 4 1 3
*/
问题网址: 点击打开链接