sql-server - 有没有一种快速的方法可以将列中的所有值转换为 True/False 值？

Question

例如我有下表

+--------+-----------------------+--+
|   Id   |    BuyingAttribute    |  |
+--------+-----------------------+--+
| 1      |    Low Price          |  |
| 1      |    Good Communication |  |
| 1      |    English Speaker    |  |
| 2      |    Low Price          |  |
| ...etc | ...etc                |  |
+--------+-----------------------+--+

有没有一种方法可以将所有值自动转换为是/否值。我知道我可以像这样使用 case 语句

SELECT
    Id,
    CASE WHEN BuyingAttribute = 'Low Price' THEN True ELSE False END AS Buying Attribute
FROM
    myTable

但是，有没有一种自动化的方法，因为如果我有50个值并写50个case语句来生成如下结果会很耗时

+----+-----------------------+---------------+-------------------------+----------------------+
| Id |  BuyingAttribute      | BA(Low Price) |  BA(Good Communication) |  BA(English Speaker) |  
+----+-----------------------+---------------+-------------------------+----------------------+
|  1 |    Low Price          |   True        |   False                 |   False              |  
|  1 |    Good Communication |   False       |   True                  |   False              |  
|  1 |    English Speaker    |   False       |   False                 |   True               |  
|  2 |    Low Price          |   True        |   False                 |   False              |  
|    |                       |               |                         |                      |  
+----+-----------------------+---------------+-------------------------+----------------------+

Answer 1

您可以像这样将所有可能的购买属性分配给所有 id 来做到这一点

select tid,row_number() over (partition by tid order by baba) rn,
        baba,
        t.BuyingAttribute tba
from
(
select distinct t.id tid, ba.buyingattribute baba from t
cross join (select distinct t.BuyingAttribute from t) ba
) s
left join t on t.BuyingAttribute = s.baba and t.id = s.tid

结果

tid         rn                   baba                           tba
----------- -------------------- ------------------------------ ------------------------------
1           1                    English Speaker                English Speaker
1           2                    Good Communication             Good Communication
1           3                    Low Price                      Low Price
2           1                    English Speaker                NULL
2           2                    Good Communication             NULL
2           3                    Low Price                      Low Price

然后我们可以将其包装在一个枢轴中

select tid,rn,
    case when [English Speaker] is not null then 'true' else 'false' end as 'English Speaker',
    case when [Good Communication] is not null then 'true' else 'false' end as 'Good Communication',
    case when [Low Price] is not null then 'true' else 'false' end as 'Low Price'
from
(
select tid,row_number() over (partition by tid order by baba) rn,
        baba,
        t.BuyingAttribute tba
from
(
select distinct t.id tid, ba.buyingattribute baba from t
cross join (select distinct t.BuyingAttribute from t) ba
) s
left join t on t.BuyingAttribute = s.baba and t.id = s.tid
--order by tid, baba
) t
pivot (max(baba) for baba in ([English Speaker],[Good Communication],[Low Price])) pvt 
order by tid,rn

得到

tid         rn                   English Speaker Good Communication Low Price
----------- -------------------- --------------- ------------------ ---------
1           1                    true            false              false
1           2                    false           true               false
1           3                    false           false              true
2           1                    true            false              false
2           2                    false           true               false
2           3                    false           false              true

这对你来说是个问题，因为你不知道你有多少购买属性，所以我们需要构建上面的语句并执行动态 sql。

declare @s1 nvarchar(max)
declare @s10 varchar(max)
declare @s9 varchar(max)

set @s1 = 'select tid,rn,'

set @s9 = (
          select top 1  STUFF((
          SELECT 'case when [' + t1.buyingattribute + '] is not null then ' + char(39) +  'true' + char(39) + 
          ' else ' + char(39) + 'false' + char(39) + ' end as ' + char(39) + t1.buyingattribute + char(39) + ','  
          from t t1 where t1.buyingattribute >= t.buyingattribute
          FOR XML PATH(''), TYPE).value('.', 'nVARCHAR(max)'), 1, 1, '')
          from 
          (select distinct buyingattribute from t) t
        )
--select substring(@s9,patindex('%,%',@s9)+ 1, len(@s9) - patindex('%,%',@s9))
--select @s9
set @s9 = substring(@s9,1,len(@s9) -1)
set @s1 = concat(@s1,substring(@s9,patindex('%,%',@s9)+ 1, len(@s9) - patindex('%,%',@s9)))


set @s10 = ' from
(
select tid,row_number() over (partition by tid order by baba) rn,
        baba,
        t.BuyingAttribute tba
from
(
select distinct t.id tid, ba.buyingattribute baba from t
cross join (select distinct t.BuyingAttribute from t) ba
) s
left join t on t.BuyingAttribute = s.baba and t.id = s.tid
--order by tid, baba
) t
pivot (max(baba) for baba in ('

set @s9 = (
          select top 1  STUFF((
          SELECT '[' + t1.buyingattribute + '] ,'  
          from t t1 where t1.buyingattribute >= t.buyingattribute
          FOR XML PATH(''), TYPE).value('.', 'nVARCHAR(max)'), 1, 1, '')
          from 
          (select distinct buyingattribute from t) t
        )
set @s9 = substring(@s9,1,len(@s9) - 1)
set @s1 = concat(@s1,@s10,substring(@s9,patindex('%,%',@s9)+ 1, len(@s9) - patindex('%,%',@s9)),' )) pvt order by tid,rn')



exec sp_executesql @s1