select a, Case when b is not null then (select b from t where a='cm') end, Case when c is not null then (select c from t where a='cm') end, Case when d is not null then (select d from t where a='cm') end, Case when e is not null then (select e from t where a='cm') end, Case when f is not null then (select f from t where a='cm') end, Case when g is not null then (select g from t where a='cm') end from t where a<>'cm'
use test go if object_id('test.dbo.tb') is not null drop table tb -- 创建数据表 create table tb ( [cm] char(4), [215] int, [220] int, [225] int, [230] int, [235] int, [240] int ) go --插入测试数据 insert into tb select 'A01',1,1,1,null,null,null union all select 'A02',null,1,null,2,1,null go --代码实现;with t as ( select cm,col=case when [215] is not null and colid=2 then name when [220] is not null and colid=3 then name when [225] is not null and colid=4 then name when [230] is not null and colid=5 then name when [235] is not null and colid=6 then name when [240] is not null and colid=7 then name else null end from tb,(select colid,name from syscolumns where name!='cm' and id=object_id('tb'))t ) select distinct result=rtrim(cm)+(select ','+col from t where cm=ttt.cm for xml path('')) from t ttt where col is not null/*测试结果result --------------------- A01,215,220,225 A02,220,230,235(2 行受影响) */
A01 1 1 1
A02 1 2 1
结果: A01,215,220,225 A02,220,230,235
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
A01 1 1 1
A02 1 2 1
结果: A01,215,220,225
A02,220,230,235
cm 220 225 230 235 240
A01 1 1 1
A02 1 1 1
结果:
A01,220,225,230
A02,225,235,240
Case when b is not null then (select b from t where a='cm') end,
Case when c is not null then (select c from t where a='cm') end,
Case when d is not null then (select d from t where a='cm') end,
Case when e is not null then (select e from t where a='cm') end,
Case when f is not null then (select f from t where a='cm') end,
Case when g is not null then (select g from t where a='cm') end
from t where a<>'cm'
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
[cm] char(4),
[215] int,
[220] int,
[225] int,
[230] int,
[235] int,
[240] int
)
go
--插入测试数据
insert into tb select 'A01',1,1,1,null,null,null
union all select 'A02',null,1,null,2,1,null
go
--代码实现;with t as
(
select cm,col=case when [215] is not null and colid=2 then name
when [220] is not null and colid=3 then name
when [225] is not null and colid=4 then name
when [230] is not null and colid=5 then name
when [235] is not null and colid=6 then name
when [240] is not null and colid=7 then name else null end
from tb,(select colid,name from syscolumns where name!='cm' and id=object_id('tb'))t
)
select distinct result=rtrim(cm)+(select ','+col from t where cm=ttt.cm for xml path(''))
from t ttt where col is not null/*测试结果result
---------------------
A01,215,220,225
A02,220,230,235(2 行受影响)
*/