CODEWITHSUNDEEP
sqlsql-server-2008
gaffcz
gaffcz

Reputation: 3479

SQL - get column names when value is 1

I have a junction table: 

id | warehouse | comp1 | comp2 | comp3 | comp4
----------------------------------------------
1  | w1        |   1   |  0    |  1    |  1    => String: 'col1,col3,col4'
2  | w2        |   1   |  1    |  0    |  1    => String: 'col1,col2,col4'
3  | w3        |   0   |  1    |  0    |  0    => String: 'col2'

Possible solution - change the junction table

ommit working with column names, just work with substrings or masks (using like %comp1% for example):

id | warehouse| companies
-----------------------------------
1  | w1       | 'comp1,comp3,comp4'
2  | w2       | 'comp3'
3  | w3       | 'comp1,comp2,comp4'

Upvotes: 2

Views: 4222

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

What you need is basically an unpivot over the column names excluded id and warehouse.

One way to do that is to use the Table Value Constructor in a sub-query that uses for xml path('') to build a concatenated list of column names.

select T1.id,
       T1.warehouse,
       stuff((
             select ','+T2.company
             from (values(T1.comp1, 'comp1'),
                         (T1.comp2, 'comp2'),
                         (T1.comp3, 'comp3'),
                         (T1.comp4, 'comp4')) as T2(value, company)
             where T2.value = 1
             for xml path('')
            ), 1, 1, '') as comp
from YourTable as T1

SQL Fiddle

The query above need to be modified when you add new columns. A query that will work with dynamic number of columns needs to be generated dynamically. You can use sys.columns to get the column names and build the query above dynamically and execute the query using execute.

declare @SQL nvarchar(max)

set @SQL = '
select T1.id,
       T1.warehouse,
       stuff((
             select '',''+T2.company
             from (values'+
                           stuff((
                                 select ',(T1.'+name, ','''+name+''')'
                                 from sys.columns
                                 where object_name(object_id) = 'YourTable' and
                                       name not in ('id', 'warehouse')
                                 for xml path('')
                                 ), 1, 1, '') +
             ') as T2(value, company)
             where T2.value = 1
             for xml path('''')
            ), 1, 1, '''') as comp
from YourTable as T1'

exec (@SQL)

SQL Fiddle

I was not entirely truthful when I said that this needs dynamic SQL. In this case it is actually possible to pull this off with with some xQuery stuff.

select id,
       warehouse,
       stuff((
             select ','+T3.N.value('local-name(.)', 'nvarchar(128)')
             from T2.X.nodes('*[not(local-name() = ("id","warehouse"))]') as T3(N)
             where T3.N.value('(./text())[1] cast as xs:boolean?', 'bit') = 1
             for xml path('')
            ), 1, 1, '') as comp
from YourTable as T1
  cross apply (
              select T1.*
              for xml path(''), type
              ) as T2(X)

SQL Fiddle

Building the comma separated column list is the same as in the previous queries using for xml path(''). Here in the cross apply there is an XML constructed for each row that is used to query the values and the element names in the sub-query. Element name corresponds to column name and is accessed using local-name(.). The values for one row is unpivoted (is that even a real word) with the nodes() expression. nodes() also makes sure that id and warehouse is not returned as columns.

Upvotes: 4

Freelancer
Freelancer

Reputation: 9074

Use case when for this.

select case when column1=1 then '1' else '0' end as column1 from tableName

Upvotes: 0

Related Questions