也就是内容相同的在一个字段里如下只是变成两个字段的,希望提供一个通用的语句:原始表,两个字段,如下:
A zhangsan
A lisi
B wangwu
B merce 查询后显示为三个字段如下:
A Null zhangsan
A Null lisi
Null B wangwu
Null B merce /*
create table test (f1 varchar(2) null,desc1 varchar(10) null)
insert into test (f1,desc1) values('A','zhangsan')
insert into test (f1,desc1) values('A','lisi')
insert into test (f1,desc1) values('B','wangwu')
insert into test (f1,desc1) values('B','merce')
*/
A zhangsan
A lisi
B wangwu
B merce 查询后显示为三个字段如下:
A Null zhangsan
A Null lisi
Null B wangwu
Null B merce /*
create table test (f1 varchar(2) null,desc1 varchar(10) null)
insert into test (f1,desc1) values('A','zhangsan')
insert into test (f1,desc1) values('A','lisi')
insert into test (f1,desc1) values('B','wangwu')
insert into test (f1,desc1) values('B','merce')
*/
insert into test (f1,desc1) values('A','zhangsan')
insert into test (f1,desc1) values('A','lisi')
insert into test (f1,desc1) values('B','wangwu')
insert into test (f1,desc1) values('B','merce') SELECT F1 ,K2=NULL ,K1=DESC1 FROM test WHERE f1='A'
union all
SELECT F1 ,K2=DESC1 ,K1=null FROM test WHERE f1='b'
/*
F1 K2 K1
---- ---------- ----------
A NULL zhangsan
A NULL lisi
B wangwu NULL
B merce NULL(4 行受影响) */
insert into test (f1,desc1) values('A','zhangsan')
insert into test (f1,desc1) values('A','lisi')
insert into test (f1,desc1) values('B','wangwu')
insert into test (f1,desc1) values('B','merce') SELECT F1 ,K2=NULL ,K1=DESC1 FROM test WHERE f1='A'
union all
SELECT NULL ,K2=F1 ,K1=null FROM test WHERE f1='b'
/*
F1 K2 K1
---- ---- ----------
A NULL zhangsan
A NULL lisi
NULL B NULL
NULL B NULL
(4 行受影响) */
insert into test (f1,desc1) values('A','zhangsan')
insert into test (f1,desc1) values('A','lisi')
insert into test (f1,desc1) values('B','wangwu')
insert into test (f1,desc1) values('B','merce')
select a=case when f1='a' then f1 end,b=case when f1='b' then f1 end,desc1 from test
go
drop table test
--author:lin
--time:mid-night
----------------------
create table test (f1 varchar(2) null,desc1 varchar(10) null)
insert into test (f1,desc1) values('A','zhangsan')
insert into test (f1,desc1) values('A','lisi')
insert into test (f1,desc1) values('B','wangwu')
insert into test (f1,desc1) values('B','merce')
insert into test (f1,desc1) values('c','fsd')
insert into test (f1,desc1) values('c','gs')
declare @sql varchar(1000)
select @sql=isnull(@sql+',','')+'case when f1='''+f1+''' then f1 end as '+f1 from test
set @sql='select '+@sql+',desc1 from test'
exec( @sql)
go
drop table test
-----------
--result
/*
A A B B c c desc1
---- ---- ---- ---- ---- ---- ----------
A A NULL NULL NULL NULL zhangsan
A A NULL NULL NULL NULL lisi
NULL NULL B B NULL NULL wangwu
NULL NULL B B NULL NULL merce
NULL NULL NULL NULL c c fsd
NULL NULL NULL NULL c c gs
*/
给我点分吧呵呵
drop table test
go
create table test (f1 varchar(2) null,desc1 varchar(10) null)
insert into test (f1,desc1) values('A','zhangsan')
insert into test (f1,desc1) values('A','lisi')
insert into test (f1,desc1) values('B','wangwu')
insert into test (f1,desc1) values('B','merce')
select f1= case when f1='A' then f1 else null end ,f1= case when f1='B' then f1 else null end
, desc1 from test
f1 f1 desc1
---- ---- ----------
A NULL zhangsan
A NULL lisi
NULL B wangwu
NULL B merce