SQL - 当值为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'

• How to get the string of column names, if the value of column is 1?

如果列的值为1,如何获取列名称字符串?

Possible solution - change the junction table

可能的解决方案 - 更改联结表

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

省略使用列名,只需使用子串或掩码(例如使用%comp1%):

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

2 个解决方案

#1

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

您需要的是基本上对列名称排除的id和仓库的不透明。

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.

一种方法是在子查询中使用表值构造函数,该子查询使用xml路径('')来构建列名称的连接列表。

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.

添加新列时,需要修改上面的查询。需要动态生成将使用动态列数的查询。您可以使用sys.columns获取列名并动态构建上面的查询,并使用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.

当我说这需要动态SQL时,我并不完全真实。在这种情况下,实际上可以通过一些xQuery的东西来解决这个问题。

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.

构建逗号分隔列列表与使用for xml path('')的先前查询中的相同。这里在交叉应用中,为每一行构造了一个XML,用于查询子查询中的值和元素名称。元素名称对应于列名称,使用local-name(。)访问。使用nodes()表达式,一行的值是不透明的(甚至是真正的单词)。 nodes()还确保不将id和仓库作为列返回。

#2

Use case when for this.

用例的用例。

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