日期:2014-05-17 浏览次数:20555 次
create table #temp(col1 int,col2 int,col3 int,col4 int,col5 int,col6 int)
insert into #temp values(1,2,0,0,8,0)
declare @s varchar(1000)
set @s=''
select top 1 @s=case when col1<>0 then ',col1' else '' end
+case when col2<>0 then ',col2' else '' end
+case when col3<>0 then ',col3' else '' end
+case when col4<>0 then ',col4' else '' end
+case when col5<>0 then ',col5' else '' end
+case when col6<>0 then ',col6' else '' end
from #temp
set @s=STUFF(@s,1,1,'')
select @s
exec ('select '+@s+ ' from #temp')
/*
col1 col2 col5
1 2 8
*/
--假設你有100個优惠金额,优惠金额1~优惠金额100
IF object_id('tempdb..#t1') is not null
DROP table #t1;
IF object_id('tempdb..##t2') is not null
DROP table ##t2;
CREATE TABLE #t1(zd VARCHAR(50))
DECLARE @i INT,@sql_1 VARCHAR(MAX),@sql_2 VARCHAR(MAX)
SELECT @i=1,@sql_1='select 住院号,床位,应收金额'
WHILE @i<=100
BEGIN
SET @sql_2='IF EXISTS(SELECT 1 FROM ##temp WHERE 优惠金额'+RTRIM(@i)+'>0) INSERT #t SELECT ''优惠金额'+RTRIM(@i)+''''
EXEC(@sql_2)
END
SELECT @sql_1=@sql_1+','+RTRIM(zd) FROM #t1
SET @sql_1=@sql_1+' into ##t2 from ##temp'
EXEC(@sql_1)
SELECT * FROM ##t2--這就是你想要的結果
-- drop table ##temp
create table ##temp
(
住院号 varchar(20),
床位 varchar(20),
应收金额 numeric(20,3),
优惠金额1 numeric(20,3),
优惠金额2 numeric(20,3),
优惠金额3 numeric(20,3),
优惠金额4 numeric(20,3)
)
insert into ##temp
select '00000','111',1000, 0,0,0,10 union all
select '00001','112',1000, 0 ,0,0,0 union all
select '00002','113',1000, 0,0,0,0 union all
select '00003','114',1000, 0 ,0,0,20 union all
select '00004','115',1000, 0,2,0,3 union all
select '00005','116',1000, 0,0,0,0 union all
select '00006','117',1000, 0,0,0,0
go
declare @sql nvarchar(max);
declare @sql_delete_column nvarchar(max);
declare @tb table(column_name nvarchar(100),rownum int)<