34,594
社区成员
发帖
与我相关
我的任务
分享
create table Table01
(字段01 bit,字段02 bit,字段03 bit)
insert Table01
select 'true', 'false', 'true' union all
select 'false', 'false', 'true'
declare @sql nvarchar(4000)
set @sql=''
select @sql=@sql+' union all select Rowid,'''+[name]+''' as ColnumName,'
+'case when '+[name]+'=''true'' then 1 else 0 end as [value] from T' from sys.columns
where [object_id]= object_id('Table01')
set @sql=';with T as (select Row_number()over(order by getdate()) as Rowid,* from Table01)'+
stuff(@sql,1,10,'')
exec(@sql)
--Rowid ColnumName value
---------------------- ---------- -----------
--1 字段01 1
--2 字段01 0
--1 字段02 0
--2 字段02 0
--1 字段03 1
--2 字段03 1
--
--(6 row(s) affected)
create table #tb(bool bit)
insert #tb
select 'true' union all
select 'false' union all
select 'false' union all
select 'true'
select case bool when 'true' then 1 else 0 end as bool from #tb
create table tb(col bit)
insert into tb select 1 union all select 0
go
select * from tb
/*
col
-----
1
0
(2 行受影响)
*/
go
drop table tb
select case value
when 'true' then 1
when 'false' then 0 end
select case when 字段='true' then 1 else 0 end as [新字段名]
select case when 字段='true' the 1 else 0 end from tb