如何在列中搜索值，并使用枢轴将其作为列

Question

select 
     distinct state,sector,
             pt.RSGHDPE as [PPC],pt.OPC43HDPE as [OPC43],
             pt.OPC53HDPE as [OPC53],pt.RSFHDPE as [RSF]
from RMCL_DESPSUM_This_year as t
pivot (min(t.factory) for t.item_name in( RSGHDPE,OPC53HDPE,OPC43HDPE,RSFHDPE)) as pt
where inv_Date between'2017-04-01 00:00:00.000'and getdate()
order by state

我从项目代码中给出了一个值，即RSGHDPE，但对于其他项目名称，我有6个项目以RSG开头。

我要找的是下面一种或任何一种方法

pivot (min(t.factory) for t.item_name like( 'RSG%','OPC53%','OPC43%','RSF%')) as pt

Answer 1

由于您正在寻找很少的item_names，所以可以使用一些条件聚合方法。

SELECT  
        state, sector,
        MIN(CASE WHEN item_name LIKE 'RSG%' THEN factory END) PPC,
        ...
        MIN(CASE WHEN item_name LIKE 'RSF%' THEN factory END) RSF
FROM RMCL_DESPSUM_This_year as t
WHERE inv_Date between @start AND @end
GROUP BY state,sector