How to build a delimited string using column names if the field is empty?

Question

I did some searching, and most returned the table names or was a bit over-kill for my needs (replacing data..if higher/lower..etc)

I have an existing table I need to work with:

id | booth_100 | booth_101 | booth_102 | booth_103 | booth_105 | booth_121  | booth_200 | booth_201...etc.

• My current understanding (didn't create this odd table) is there will only be '1' row
• Only '1' of the 'booth_xxx' columns will have a value in it.

How can I build a pipe (|) delimited string using ONLY the 'booth_' column names IF it doesn't have a value in it?

id | booth_100 | booth_101 | booth_102 | booth_103 | booth_105 | booth_121  | booth_200 | booth_201

1  |    1      |    0      |     0     |      0    |     0          1       |     1     |     0

I would like to figure out how to get a returned results of:

booth_100|booth_121|booth200

as my returned query/string.

Is there an easy way to do this? (or some long way that needs to list out every column name in the query itself?.. which I'd still like help with if that's the only route)

SQL Fiddle for table example:

Another example/attempt: (but this one REQUIRES the columns to be bit not int?)..if I use int for col type. the query part fails? (not sure what I am doing wrong though?)

http://sqlfiddle.com/#!18/f80c8/1/0

Answer 1

Your title says "SQL Server" but your sqlfiddle uses MySQL so I'm hoping it is SQL Server you want. The non-normalized table you are using really should be "flipped" or "unpivotted" which you can do using a cross apply as shown below. Once you have the data in a more normalized shape, then you can use STRING_AGG() provided you have SQL Server 2017 or later.

SQL Fiddle

SQL Server 2017 Schema Setup:

create table exhibitors_booth(
  id int primary key,
  booth_100 int,
  booth_101 int,
  booth_102 int,
  booth_103 int,
  booth_104 int,
  booth_200 int,
  booth_201 int,
  booth_202 int,
  booth_203 int
);

insert into exhibitors_booth values
('1', 1, 0, 1, 1, 0, 0, 0, 0, 1);

SELECT
    CrossApplied.*
into exhibitors
FROM exhibitors_booth
CROSS APPLY (
    VALUES 
                (1, 'booth_100', booth_100)
              , (2, 'booth_101', booth_101)
              , (3, 'booth_102', booth_102)
              , (4, 'booth_103', booth_103)
              , (5, 'booth_104', booth_104)
              , (6, 'booth_201', booth_200)
              , (7, 'booth_202', booth_201)
              , (8, 'booth_203', booth_202)
              , (9, 'booth_204', booth_203)
            ) AS CrossApplied(SeqNo, Booth, YesNo)
;

Query 1:

select * from exhibitors_booth

Results:

| id | booth_100 | booth_101 | booth_102 | booth_103 | booth_104 | booth_200 | booth_201 | booth_202 | booth_203 |
|----|-----------|-----------|-----------|-----------|-----------|-----------|-----------|-----------|-----------|
|  1 |         1 |         0 |         1 |         1 |         0 |         0 |         0 |         0 |         1 |

Query 2:

select * from exhibitors

Results:

| SeqNo |     Booth | YesNo |
|-------|-----------|-------|
|     1 | booth_100 |     1 |
|     2 | booth_101 |     0 |
|     3 | booth_102 |     1 |
|     4 | booth_103 |     1 |
|     5 | booth_104 |     0 |
|     6 | booth_201 |     0 |
|     7 | booth_202 |     0 |
|     8 | booth_203 |     0 |
|     9 | booth_204 |     1 |

Query 3:

select
string_agg(booth,',') as booths
from exhibitors

Results:

|                                                                                    booths |
|-------------------------------------------------------------------------------------------|
| booth_100,booth_101,booth_102,booth_103,booth_104,booth_201,booth_202,booth_203,booth_204 |

I am assuming this is the wanted result. If that's not true please modify your question to include the result you are seeking.

If you do not want to use the "flipped" table just place that code into a cte (or derived table), like this:

with cte as (
    SELECT
        CrossApplied.*
    FROM exhibitors_booth
    CROSS APPLY (
        VALUES 
                    (1, 'booth_100', booth_100)
                  , (2, 'booth_101', booth_101)
                  , (3, 'booth_102', booth_102)
                  , (4, 'booth_103', booth_103)
                  , (5, 'booth_104', booth_104)
                  , (6, 'booth_201', booth_200)
                  , (7, 'booth_202', booth_201)
                  , (8, 'booth_203', booth_202)
                  , (9, 'booth_204', booth_203)
                ) AS CrossApplied(SeqNo, Booth, YesNo)
      )
select
string_agg(booth,',') as booths
from cte
;