select All= (case when A is not null then rtrim(A)+',' end)+ (case when B is not null then rtrim(B)+',' end)+ (case when C is not null then rtrim(C)+',' end)+ (case when D is not null then rtrim(D)+',' end)+ (case when E is not null then rtrim(E)+',' end)+ (case when F is not null then rtrim(F)+',' end) from tb
create table tb(A varchar(10),B varchar(10),C varchar(10),D varchar(10),E varchar(10),F varchar(10)) insert into tb select '1','2','3',null,null,null insert into tb select null,'3','4','5','6','7' go select left(allcolumn,len(allcolumn)-1) as [ALL] from( select replace(isnull(A,'_')+','+isnull(B,'_')+','+isnull(C,'_')+','+isnull(D,'_')+','+isnull(E,'_')+','+isnull(F,'_')+',','_,','')as allcolumn from tb )t go drop table tb /* ALL ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1,2,3 3,4,5,6,7(2 行受影响)*/
create table tb1 (a int,b int,c int,d int)insert into tb1 select 1,null,2,3 union all select 2,3,null,nullselect stuff((case when A is not null then ','+rtrim(A) else '' end)+ (case when B is not null then ','+rtrim(B) else '' end)+ (case when C is not null then ','+rtrim(C) else '' end)+ (case when D is not null then ','+rtrim(D) else '' end),1,1,'') as 'All' from tb1/* All -------- 1,2,3 2,3
--> 测试数据:# if object_id('tempdb.dbo.#') is not null drop table # create table #(A varchar(10), B varchar(10), C varchar(10), D varchar(10), E varchar(10), F varchar(10)) insert into # select 1, null, 2, null, null, 3 union all select null, 3, 4, 5, 6, 7select *, stuff(isnull(','+A,'')+isnull(','+B,'')+isnull(','+C,'')+isnull(','+D,'')+isnull(','+E,'')+isnull(','+F,''),1,1,'')[ALL] from #/* A B C D E F ALL ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 NULL 2 NULL NULL 3 1,2,3 NULL 3 4 5 6 7 3,4,5,6,7 */
create table tb(A varchar(10),B varchar(10),C varchar(10),D varchar(10),E varchar(10),F varchar(10)) insert into tb select '1','2','3',null,null,null insert into tb select null,'3','4','5','6','7' go select left([ALL],len([ALL])-1) as [ALL] from( select isnull(A+',','')+isnull(B+',','')+isnull(C+',','')+isnull(D+',','')+isnull(E+',','')+isnull(F+',','') as [ALL] from tb )t go drop table tb /* ALL ------------------------------------------------------------------------ 1,2,3 3,4,5,6,7(2 行受影响)*/
drop table tb create table tb(A varchar(10),B varchar(10),C varchar(10),D varchar(10),E varchar(10),F varchar(10)) insert into tb select '1','2','3',null,null,null insert into tb select null,'3','4','5','6','7' goalter table tb add [all] varchar(200)update tb set [all]=stuff(isnull(','+A,'')+isnull(','+B,'')+isnull(','+C,'')+isnull(','+D,'')+isnull(','+E,'')+isnull(','+F,''),1,1,'')
select *, [All] = reverse(stuff(reverse( case when isnull(A, '') = '' then '' else A + ',' end + case when isnull(B, '') = '' then '' else B + ',' end + case when isnull(C, '') = '' then '' else C + ',' end + case when isnull(D, '') = '' then '' else D + ',' end + case when isnull(E, '') = '' then '' else E + ',' end + case when isnull(F, '') = '' then '' else F + ',' end ), 1, 1, '')) FROM tb
select All= (case when A is not null then rtrim(A)+',' end)+
(case when B is not null then rtrim(B)+',' end)+
(case when C is not null then rtrim(C)+',' end)+
(case when D is not null then rtrim(D)+',' end)+
(case when E is not null then rtrim(E)+',' end)+
(case when F is not null then rtrim(F)+',' end) from tb
insert into tb select '1','2','3',null,null,null
insert into tb select null,'3','4','5','6','7'
go
select left(allcolumn,len(allcolumn)-1) as [ALL] from(
select replace(isnull(A,'_')+','+isnull(B,'_')+','+isnull(C,'_')+','+isnull(D,'_')+','+isnull(E,'_')+','+isnull(F,'_')+',','_,','')as allcolumn from tb
)t
go
drop table tb
/*
ALL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3
3,4,5,6,7(2 行受影响)*/
create table tb1 (a int,b int,c int,d int)insert into tb1
select 1,null,2,3 union all
select 2,3,null,nullselect stuff((case when A is not null then ','+rtrim(A) else '' end)+
(case when B is not null then ','+rtrim(B) else '' end)+
(case when C is not null then ','+rtrim(C) else '' end)+
(case when D is not null then ','+rtrim(D) else '' end),1,1,'') as 'All'
from tb1/*
All
--------
1,2,3
2,3
if object_id('tempdb.dbo.#') is not null drop table #
create table #(A varchar(10), B varchar(10), C varchar(10), D varchar(10), E varchar(10), F varchar(10))
insert into #
select 1, null, 2, null, null, 3 union all
select null, 3, 4, 5, 6, 7select *, stuff(isnull(','+A,'')+isnull(','+B,'')+isnull(','+C,'')+isnull(','+D,'')+isnull(','+E,'')+isnull(','+F,''),1,1,'')[ALL] from #/*
A B C D E F ALL
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 NULL 2 NULL NULL 3 1,2,3
NULL 3 4 5 6 7 3,4,5,6,7
*/
insert into tb select '1','2','3',null,null,null
insert into tb select null,'3','4','5','6','7'
go
select left([ALL],len([ALL])-1) as [ALL] from(
select isnull(A+',','')+isnull(B+',','')+isnull(C+',','')+isnull(D+',','')+isnull(E+',','')+isnull(F+',','') as [ALL] from tb
)t
go
drop table tb
/*
ALL
------------------------------------------------------------------------
1,2,3
3,4,5,6,7(2 行受影响)*/
drop table tb
create table tb(A varchar(10),B varchar(10),C varchar(10),D varchar(10),E varchar(10),F varchar(10))
insert into tb select '1','2','3',null,null,null
insert into tb select null,'3','4','5','6','7'
goalter table tb add [all] varchar(200)update tb
set [all]=stuff(isnull(','+A,'')+isnull(','+B,'')+isnull(','+C,'')+isnull(','+D,'')+isnull(','+E,'')+isnull(','+F,''),1,1,'')
*,
[All] = reverse(stuff(reverse(
case when isnull(A, '') = '' then '' else A + ',' end +
case when isnull(B, '') = '' then '' else B + ',' end +
case when isnull(C, '') = '' then '' else C + ',' end +
case when isnull(D, '') = '' then '' else D + ',' end +
case when isnull(E, '') = '' then '' else E + ',' end +
case when isnull(F, '') = '' then '' else F + ',' end
), 1, 1, ''))
FROM tb