Select column names that match a criteria (MS SQL Server)

Question

I have the following database structure in MS SQL Server: ID, Col_A, Col_B, Col_C, etc...

All the other columns except for ID are of type Boolean. Lets say for example that Col_A = 1, Col_B = 0, Col_C = 1

I am looking for a way to return the names of the columns where the column is 1. In this example the return should look something like ID, Col_A, Col_C

There will be a dynamic number of columns, seeing as the table is altered often to add new columns and delete old ones.

Basically, I need the exact same functionality as in the following post, but as a MS Sql Server query: Select column names that match a criteria (MySQL)

The SQL Fiddle link http://sqlfiddle.com/#!2/8f4ee/12 is what I want to implement in MS SQL Server. Any ideas how I would go about it? The two functions, CONCAT_WS and GROUP_CONCAT are not recognized by MS SQL Server.

Any help would be appreciated.

Please [edit] your question and ask it here (including the relevant parts of the code). If most of the content is in links somewhere else, it's not searchable by future readers, and it's also meaningless if the links (especially the one pointing off-site) are not available. Questions here should stand on their own, with any links being additional references; the links should not be the primary content. Thanks. — Ken White, Jul 19 '13 at 21:29

score 0 · Answer 1 · answered Jul 19 '13 at 22:09

try this:

create table jtr (id varchar(36), col_a tinyint, col_b tinyint, col_c tinyint)
insert into jtr values (1, 1, 0, 1), (2, 0, 0, 1), (3, 1, 0 ,0), (4, 0, 1, 0)

CREATE TABLE #app (res tinyint)
CREATE TABLE #outmess (message varchar(1000))

DECLARE @dynsql varchar(1000)
DECLARE @colname sysname
DECLARE @mess varchar(1000)
DECLARE @id int

DECLARE #crs_tab INSENSITIVE CURSOR FOR
SELECT id FROM jtr
FOR READ ONLY

OPEN #crs_tab
FETCH NEXT FROM #crs_tab INTO @id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE #crs INSENSITIVE CURSOR FOR
    SELECT c.name FROM syscolumns c
    JOIN sysobjects o
    ON o.id = c.id
    WHERE o.name = 'jtr'
    AND o.xtype = 'U'
    AND c.type = 38
    FOR READ ONLY

    OPEN #crs
    FETCH NEXT FROM #crs INTO @colname
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @dynsql = 'SELECT ' + @colname + ' FROM jtr where id = ' + CONVERT(varchar, @id)
        insert into #app
        exec (@dynsql)

        if (select res from #app) = 1
        begin
            if (@mess != '')
            begin
                set @mess = @mess + ', ' + @colname
            end
            else
            begin
                set @mess = @colname
            end
        end

        delete from #app
        FETCH NEXT FROM #crs INTO @colname
    END
    CLOSE #crs
    DEALLOCATE #crs
    insert into #outmess values (@mess)

    set @mess = ''
    FETCH NEXT FROM #crs_tab INTO @id
END
CLOSE #crs_tab
DEALLOCATE #crs_tab

select * from #outmess

I've created an example table JTR with these fields: ID, COL_A, COL_B, COL_C if you want you can put how many fields you want.

I put 4 rows in that table.

So I've implemented two cursor, one to scroll JTR table and one to scroll all the column name from syscolumns.

For every column valued ONE i build a message with column name

In the temporary table #outmess you find all the column valued ONE concatenated by comma.

P.S. Instead boolean I've used tinyint. In syscolumns field type valued 38 about tinyint

Tell me if it's ok

score 0 · Accepted Answer · answered Jul 19 '13 at 22:16

This is pretty straight-forward (SQLFiddle: http://sqlfiddle.com/#!3/c44bc/1/0):

DECLARE @cmd NVARCHAR(MAX);
DECLARE @cmd_partial NVARCHAR(MAX);

SET @cmd_partial = '';

SELECT @cmd_partial = @cmd_partial + '
       CASE ' + COLUMN_NAME + '
          WHEN 1 THEN
             ''' + COLUMN_NAME + ', ''
          ELSE
             ''''
       END +'
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'mytable'
   AND COLUMN_NAME <> 'id';

SET @cmd_partial = LEFT(@cmd_partial, LEN(@cmd_partial) - 1);

SET @cmd = '
WITH cols AS (
 SELECT id,
' + @cmd_partial + ' AS columns
FROM mytable
)
  SELECT id, CASE
                WHEN LEN(columns) > 0 THEN
                   LEFT(columns, LEN(columns) - 1)
                ELSE
                   ''''
             END
    FROM cols;
';

EXEC (@cmd);

score 0 · Answer 3 · answered Jul 19 '13 at 22:26

Below query is closely equivalent to your MySql query. Working demo

Query:

  declare @sql nvarchar(max)
  select @sql = ''
  select @sql = @sql +   case WHEN len(@sql) = 0 THEN '' ELSE ' + ' END + 
  ' case when ' + C.Name  + ' = 1 then '',' + C.Name  + ''' else '''' end'
  FROM
    sys.columns c
    inner join sys.objects o on o.Object_ID = c.Object_ID 
  WHERE
    o.Name ='yourtable'
    AND C.Name!='id'


  select @sql = 'select ID, Stuff(' + @sql + ',1,1,N'''') from yourtable'
  exec (@sql)

score 0 · Answer 4 · answered Jul 19 '13 at 22:48

One of the possible solutions is dynamic sql unpivoting data with further concatenation:

declare @stmt nvarchar(max)

select @stmt = IsNull(@stmt + ', ', '') + quotename(name)
from sys.columns
where object_id = object_id('TableName') and name != 'ID'
order by column_id

set @stmt = ';with du as (
    select *
    from TableName d
        unpivot (Val for Name in (' + @stmt + ')) u
)
select d.ID, IsNull(left(Cols, len(Cols) - 1), ''<None>'') as ColumnsSet
from (select ID from TableName) d
    cross apply (select (select Name + '', ''
        from du
        where Val = 1 and ID = d.ID
        for xml path ('''')) as Cols) c'

print @stmt
exec(@stmt)

For data like:

create table TableName (ID int, Col1 bit, Col2 bit, Col3 bit)
insert into TableName values
    (1, 0, 1, 1)
    ,(2, 1, 1, 1)
    ,(3, 1, 0, 0)
    ,(4, 0, 0, 0)

Output is:

ID          ColumnsSet
----------- -----------------
1           Col2, Col3
2           Col1, Col2, Col3
3           Col1
4           <None>

Select column names that match a criteria (MS SQL Server)

4 Answers4