举个例子:
表结构为CD 列1 列2 列3 列4
1 1 1 1 1
2 1 1 0 1
3 1 0 1 1
4 1 0 0 1
5 0 1 1 1
6 0 1 0 1
7 0 0 1 1
8 0 0 0 1
9 1 1 1 0
10 1 1 0 0
11 1 0 1 0
12 1 0 0 0
13 0 1 1 0
14 0 1 0 0
15 0 0 1 0
16 0 0 0 0查询结果
列1 列2
1 A,B,C,D
2 A,B,D
.......
14 B
15 C
16 null也就是,如果字段的值是1 就显示出对应的名字(A),是0 则不显示了,用逗号来分割
表结构为CD 列1 列2 列3 列4
1 1 1 1 1
2 1 1 0 1
3 1 0 1 1
4 1 0 0 1
5 0 1 1 1
6 0 1 0 1
7 0 0 1 1
8 0 0 0 1
9 1 1 1 0
10 1 1 0 0
11 1 0 1 0
12 1 0 0 0
13 0 1 1 0
14 0 1 0 0
15 0 0 1 0
16 0 0 0 0查询结果
列1 列2
1 A,B,C,D
2 A,B,D
.......
14 B
15 C
16 null也就是,如果字段的值是1 就显示出对应的名字(A),是0 则不显示了,用逗号来分割
(case when 列1 = 1 then 'A' else '' end) +
(case when 列2 = 1 then 'B' else '' end) +
(case when 列3 = 1 then 'C' else '' end) +
(case when 列4 = 1 then 'D' else '' end)
from tb
insert into tb values(1 ,1, 1, 1, 1)
insert into tb values(2 ,1, 1, 0, 1)
insert into tb values(3 ,1, 0, 1, 1)
insert into tb values(4 ,1, 0, 0, 1)
insert into tb values(5 ,0, 1, 1, 1)
insert into tb values(6 ,0, 1, 0, 1)
insert into tb values(7 ,0, 0, 1, 1)
insert into tb values(8 ,0, 0, 0, 1)
insert into tb values(9 ,1, 1, 1, 0)
insert into tb values(10 ,1, 1, 0, 0)
insert into tb values(11 ,1, 0, 1, 0)
insert into tb values(12 ,1, 0, 0, 0)
insert into tb values(13 ,0, 1, 1, 0)
insert into tb values(14 ,0, 1, 0, 0)
insert into tb values(15 ,0, 0, 1, 0)
insert into tb values(16 ,0, 0, 0, 0)
go
select 列1 = cd , 列2 =
(case when 列1 = 1 then 'A' else '' end) +
(case when 列2 = 1 then 'B' else '' end) +
(case when 列3 = 1 then 'C' else '' end) +
(case when 列4 = 1 then 'D' else '' end)
from tb drop table tb/*
列1 列2
----------- ----
1 ABCD
2 ABD
3 ACD
4 AD
5 BCD
6 BD
7 CD
8 D
9 ABC
10 AB
11 AC
12 A
13 BC
14 B
15 C
16 (所影响的行数为 16 行)
*/
create table T(CD int identity(1,1),列1 int,列2 int,列3 int,列4 int)
insert into T select 1,1,1,1
insert into T select 1,1,0,1
insert into T select 1,0,1,1
insert into T select 1,0,0,1
insert into T select 0,1,1,1
insert into T select 0,1,0,1
insert into T select 0,0,1,0
insert into T select 0,0,0,1
insert into T select 0,0,0,0
insert into T select 0,1,1,0select 列1, case when 列2='' then null
else case when right(列2,1)=','
then left(列2,len(列2)-1)
else 列2 end
end as 列2
from
(
select CD as 列1,
case when 列1=1 then 'A,' else '' end
+case when 列2=1 then 'B,' else '' end
+case when 列3=1 then 'C,' else '' end
+case when 列4=1 then 'D' else '' end as 列2
from T
) a/*
列1 列2
----------- -------
1 A,B,C,D
2 A,B,D
3 A,C,D
4 A,D
5 B,C,D
6 B,D
7 C
8 D
9 NULL
10 B,C
*/drop table T
insert into tb values(1 ,1, 1, 1, 1)
insert into tb values(2 ,1, 1, 0, 1)
insert into tb values(3 ,1, 0, 1, 1)
insert into tb values(4 ,1, 0, 0, 1)
insert into tb values(5 ,0, 1, 1, 1)
insert into tb values(6 ,0, 1, 0, 1)
insert into tb values(7 ,0, 0, 1, 1)
insert into tb values(8 ,0, 0, 0, 1)
insert into tb values(9 ,1, 1, 1, 0)
insert into tb values(10 ,1, 1, 0, 0)
insert into tb values(11 ,1, 0, 1, 0)
insert into tb values(12 ,1, 0, 0, 0)
insert into tb values(13 ,0, 1, 1, 0)
insert into tb values(14 ,0, 1, 0, 0)
insert into tb values(15 ,0, 0, 1, 0)
insert into tb values(16 ,0, 0, 0, 0)
go
select 列1,列2 = left(列2,len(列2) - 1) from
(
select 列1 = cd , 列2 =
(case when 列1 = 1 then 'A,' else '' end) +
(case when 列2 = 1 then 'B,' else '' end) +
(case when 列3 = 1 then 'C,' else '' end) +
(case when 列4 = 1 then 'D,' else '' end)
from tb
) t
where len(列2) > 0
drop table tb/*
列1 列2
----------- --------
1 A,B,C,D
2 A,B,D
3 A,C,D
4 A,D
5 B,C,D
6 B,D
7 C,D
8 D
9 A,B,C
10 A,B
11 A,C
12 A
13 B,C
14 B
15 C(所影响的行数为 15 行)
*/