22,207
社区成员
发帖
与我相关
我的任务
分享
if not object_id('T') is null
drop table T
Go
Create table T([col0] nvarchar(1),[col1] decimal(18,2),[col2] int,[col3] int)
Insert T
select N'a',null,1,null union all
select N'b',1.25,null,null union all
select N'c',null,null,5
go
declare @s nvarchar(4000)
select
@s=isnull(@s,' case ')+' when '+quotename(Name)+' is not null then '+quotename(Name,'''')
from syscolumns
where ID=object_id('T') and Name not in('col0')
exec ('select [col0],COl='+@s+' end from T ')
(3 個資料列受到影響)
col0 COl
---- ----
a col2
b col1
c col3
(3 個資料列受到影響)
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([col0] nvarchar(1),[col1] decimal(18,2),[col2] int,[col3] int)
Insert #T
select N'a',null,1,null union all
select N'b',1.25,null,null union all
select N'c',null,null,5
Go
Select col0,coalesce([col1],[col2],[col3]) as COl from #T
(3 個資料列受到影響)
col0 COl
---- ---------------------------------------
a 1.00
b 1.25
c 5.00
(3 個資料列受到影響)
create table tb(col0 varchar(50),col1 int,col2 int,col3 int)
insert into tb select 'a',null,1,null
insert into tb select 'b',1.25,null,null
insert into tb select 'c',null,null,5
select
col0,
colx=case when col1 is not null then 'col1' when col2 is not null then 'col2' when col3 is not null then 'col3' end
from tb
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([col0] nvarchar(1),[col1] decimal(18,2),[col2] int,[col3] int)
Insert #T
select N'a',null,1,null union all
select N'b',1.25,null,null union all
select N'c',null,null,5
Go
Select col0,[col1] from #T where COl1 is not null
union all
Select col0,[col2] from #T where COl2 is not null
union all
Select col0,[col3] from #T where COl3 is not null
order by col0
(3 個資料列受到影響)
col0 col1
---- ---------------------------------------
a 1.00
b 1.25
c 5.00
(3 個資料列受到影響)