判断多个字段的值组合新字段

[-Arvin]

表如图，查询表 需判断TimeNode1--TimeNode12 如内容为“1” 则组合成字段TimeNode内容如‘1,2,3,5,8,12’

[-Arvin]

字段TimeNode1--TimeNode12为12个月 ，字段内容1或0 1代表选中 0代表否选 所以希望根据12个月的字段查询出 TimeNode内容为‘1,2,3,5,8,12’ 方便我绑定复选框。

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([TimeNode1] int,[TimeNode2] int,[TimeNode3] int,[TimeNode4] int,[TimeNode5] int,[TimeNode6] int,[TimeNode7] int,[TimeNode8] int,[TimeNode9] int,[TimeNode10] int,[TimeNode11] int,[TimeNode12] int)
Insert #T
select 1,1,1,0,1,0,0,1,0,0,0,1 UNION
SELECT 1,1,0,0,0,0,0,0,0,0,0,0
Go
--测试数据结束
SELECT  CASE WHEN TimeNode1 = 1 THEN '1'
             ELSE ''
        END + CASE WHEN TimeNode2 = 1 THEN ',2'
                   ELSE ''
              END + CASE WHEN TimeNode3 = 1 THEN ',3'
                         ELSE ''
                    END + CASE WHEN TimeNode4 = 1 THEN ',4'
                               ELSE ''
                          END + CASE WHEN TimeNode5 = 1 THEN ',5'
                                     ELSE ''
                                END + CASE WHEN TimeNode6 = 1 THEN ',6'
                                           ELSE ''
                                      END + CASE WHEN TimeNode7 = 1 THEN ',7'
                                                 ELSE ''
                                            END
        + CASE WHEN TimeNode8 = 1 THEN ',8'
               ELSE ''
          END + CASE WHEN TimeNode9 = 1 THEN ',9'
                     ELSE ''
                END + CASE WHEN TimeNode10 = 1 THEN ',10'
                           ELSE ''
                      END + CASE WHEN TimeNode11 = 1 THEN ',11'
                                 ELSE ''
                            END + CASE WHEN TimeNode12 = 1 THEN ',12'
                                       ELSE ''
                                  END AS TimeNode
FROM    #T

[学海无涯-回头是岸]

case when 判断吧

[道素]

with t as (
    select 1 as ID,1 as TimeNode1,1 as TimeNode2,1 as TimeNode3,0 as TimeNode4,1 as TimeNode5,1 as TimeNode6,0 as TimeNode7,0 as TimeNode8,1 as TimeNode9,1 as TimeNode10,0 as TimeNode11,0 as TimeNode12 union all
    select 2,1,0,0,1,1,0,1,1,1,0,0,1
 )
 select t.ID,stuff(o.s,1,1,'') from t
 outer apply( 
     select ','+ltrim(c.seq) 
     from (values(1,TimeNode1),(2,TimeNode2),(3,TimeNode3),(4,TimeNode4),(5,TimeNode5),(6,TimeNode6),(7,TimeNode7),(8,TimeNode8),(9,TimeNode9),(10,TimeNode10),(11,TimeNode11),(12,TimeNode2))c(seq,val)
     where c.val=1 for xml path('')
 ) o(s)

  	ID	(No column name)
1	1	1,2,3,5,6,9,10,12
2	2	1,4,5,7,8,9