从多个sql列中逗号分隔的列表

问题描述：

使用SQL Server 2012，我有一个包含多个复选框字段的表格，其值显然是1或0。我需要拉这些字段的任何地方的任何值都是1从多个sql列中逗号分隔的列表

所以：

ID  Building   Heavy   Municipal  Industry 
101  1     1    0    1

结果：

ID    Type 
101    Building, Heavy, Industry

我不能为我的生活搞的语法。

答

假设数列是少数可以只使用IIF 2012+否则情况下

子串来确定起始位置和结束。

select ID, 
    SUBSTRING(
    IIF(Building=1,', Building','') + 
      IIF(Heavy=1,', Heavy','') + 
      IIF(Municipal=1,', Municipal','') + 
      IIF(Industry=1,', Industry','') 
      ,3,100) -- substring to start from pos 3 and ends at position 100 depends on the desired length 
      Type 
from table

我认为级联必须改进，否则你可能会结束：'BuildingHeavyMunicipalIndustry'还是我错了？哦，+1为简单的想法，虽然 –

有一个额外的逗号..我会改善这个欢呼声 – maSTAShuFu

答

您还可以使用CASE语句，像这样：

create table test (
    id int, 
    building int, 
    heavy int, 
    municipal int, 
    industry int 
); 
insert into test values (101, 1, 1, 0, 1); 

with data as (
    select id, 
     case when building = 1 then 'Building,' else '' end + 
     case when heavy = 1 then 'Heavy,' else '' end + 
     case when municipal = 1 then 'Municipal,' else '' end + 
     case when industry = 1 then 'Industry,' else '' end as fld 
    from test 
) 
select id, left(fld, len(fld)-1) as fld from data;

例子：http://rextester.com/CKGES46149

结果：

id fld 
101 Building,Heavy,Industry

如果需要逗号后的空间，加稍微修改如下：

with data as (
    select id, 
     rtrim(
     case when building = 1 then 'Building, ' else '' end + 
     case when heavy = 1 then 'Heavy, ' else '' end + 
     case when municipal = 1 then 'Municipal, ' else '' end + 
     case when industry = 1 then 'Industry, ' else '' end 
     ) as fld 
    from test 
) 
select id, left(fld, len(fld)-1) as fld from data;

结果：

id fld 
101 Building, Heavy, Industry

实施例：http://rextester.com/OJNEQ98420

答

一种方式做到这一点是，步骤1.逆透视表

SELECT ID, Sub, SubVal 
INTO #t2 
FROM (SELECT * FROM #t)t 
UNPIVOT 
(
    SubVal FOR Sub IN (Building,Heavy, Muncipal, Industry) 
) as un

第2步：使用FOR XML PATH，

SELECT DISTINCT ID, 
    STUFF((
     SELECT ' , ' + t2.Sub 
     FROM #t2 t2 
     WHERE SubVal = 1 
     FOR XML PATH('') 
    ), 1, 2, '') AS Type 
FROM #t2 ct

从多个sql列中逗号分隔的列表

相关推荐