我希望以矩阵形式创建一个表(如下所示),其中列标题为变量。
@ACTable AS ACT
@WCTable AS WCT
临时表,派生自@ProductionTable AS PT
我正在寻找的输出看起来像这样。本质上,我希望ACT.AC作为列标题运行,WCT.WC向下运行,并计算11月6日有多少ActFin。颜色显示匹配的关联。剩下的我会合并,不要太在意NULL或0。
到目前为止的查询(在FOR语句中失败)
SELECT * FROM
(
SELECT
PT.ParentPart,
ACT.AC,
WCT.WC,
PT.ActFin
FROM @ProductionTable AS PT
INNER JOIN @WCTable AS WCT ON WCT.WC = PT.WC
INNER JOIN @ACTable AS ACT ON PT.AC = ACT.AC
) t
PIVOT(
COUNT(CASE
WHEN
PT.ActFin > '2019-11-06' --count
THEN
1
END)
FOR ACT.AC IN ( --this is where things fall apart
'54',
'53',
'52')
)
这可能吗?
FOR
子句中的列需要包装在[]
:
SELECT * FROM
(
SELECT
PT.ParentPart,
ACT.AC,
WCT.WC,
PT.ActFin
FROM @ProductionTable AS PT
INNER JOIN @WCTable AS WCT ON WCT.WC = PT.WC
INNER JOIN @ACTable AS ACT ON PT.AC = ACT.AC
) t
PIVOT(
COUNT(CASE
WHEN
PT.ActFin > '2019-11-06' --count
THEN
1
END)
FOR ACT.AC IN ( --this is where things fall apart
[54],
[53],
[52])
)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句