Comma separated list from multiple sql columns

Question

Using SQL Server 2012, I have a table containing multiple checkbox fields where the values are 1 or 0, obviously. I need to pull any of these fields where any of these values are 1.

So:

ID      Building            Heavy         Municipal       Industry
101        1                   1              0               1

Results:

ID               Type
101              Building, Heavy, Industry

I cannot for the life of me figure out the syntax.

RoMEoMusTDiE · Answer 1 · 2017-04-19T03:23:22.877

2

assuming the number columns are few can just use IIF 2012+ otherwise case

Substring to determine starting position and ending.

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

edited Apr 19 '17 at 03:23

answered Apr 19 '17 at 03:06

RoMEoMusTDiE

4,739
1
17
26

I think that the concatenation must be improved, otherwise you may end up with: `BuildingHeavyMunicipalIndustry` or Am I wrong? Ooh +1 for the simple idea though – Jorge Campos Apr 19 '17 at 03:13
there is an extra comma.. i will improve this cheers – RoMEoMusTDiE Apr 19 '17 at 03:14

score 2 · Answer 2 · answered Apr 19 '17 at 05:47

One way to do this is, Step 1. Unpivot the table

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

Step 2: Use for 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

score 0 · Answer 3 · answered Apr 19 '17 at 03:36

You can also use CASE statement like so:

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;

Example: http://rextester.com/CKGES46149

Result:

id  fld
101 Building,Heavy,Industry

If a space after comma is needed, add a slight modification like so:

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;

Result:

id  fld
101 Building, Heavy, Industry

Example: http://rextester.com/OJNEQ98420

Comma separated list from multiple sql columns

3 Answers3