求条SQL语句，诸位帮忙。

[sdlwhgw]

举个例子：
表结构为

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 则不显示了，用逗号来分割

[vanjayhsu]

没搞明白LZ是什么意思，请LZ再详细下。

[dawugui]

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 

[dawugui]

create table tb(CD int, 列1 int , 列2 int , 列3 int , 列4 int )
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 行）
*/

[playwarcraft]

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,0

select 列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

[dawugui]

create table tb(CD int, 列1 int , 列2 int , 列3 int , 列4 int )
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 行）
*/

[sdlwhgw]

谢谢,

[sdlwhgw]

不到5天没法加分？

[sdlwhgw]

好像最后一条没有显示

[sdlwhgw]

帖子加分 怎么会没有权限呢？