透视查询辅助

Question

Job Batch id   Company Outlet Id   Clearance required   Hanky required 
1              10                  T                    T

现在，我想要以下内容

Job Batch id   Company Outlet ID    Reason    
1              10                   Clearance Required , Hanky Required 

我的大脑已经冻结了，所以需要帮助吗？

如何构造这个非透视查询？

Answer 1

这就对了--就像这样组合你的列。我正在使用内容删除开头的逗号：

select JobBatchId, 
  CompanyOutletId,
  STUFF(
    ISNULL(CASE WHEN ClearanceRequired = 'T' THEN ',Clearance Required' END, '')  +
    ISNULL(CASE WHEN HankyRequired = 'T' THEN ',Hanky Required' END, '') 
    , 1, 1, '') Reasons
from YourTable

还有SQL Fiddle。

Answer 2

我建议不要在Sql查询中的单个列(Reason)中放入多个值，而是将其留给表示层，如果您希望以这种方式查看数据的话……

但下面是如何在Sql中执行此操作：

SELECT
    [Job Batch Id],
    [Company Outlet Id],
    CASE 
        WHEN [Clearance Required] = 'T' 
        THEN 'Clearance Required' 
        ELSE '' END +
    -- Determine if the comma is needed or not...
    CASE 
        WHEN [Clearance Required] = 'T' 
            AND [Hanky Required] = 'T' 
        THEN ' , ' 
        ELSE '' END +
    CASE
        WHEN [Hanky Required] = 'T' 
        THEN 'Hanky Required' 
        ELSE '' END AS Reason
FROM YourTable

Answer 3

尝试：

select [Job Batch id], [Company Outlet Id],
       case [Clearance required]
            when 'T' then 'Clearance Required' + 
                case [Hanky required] when 'T' then ' , ' else '' end
       else ''
       end + case [Hanky required] when 'T' then 'Hanky Required' else '' end as [Reason]
from theTable