sql-server - 一个简单的枢轴但我无法理解它 - 请协助.. :-)

Question

我尝试在 MS SSMS 中做一个简单的转换，但无法弄清楚。

这是我的数据集:

Date        CallerANI      Skills               CallerDNIS
2019-05-27  27315391449    loc-SPD-CSR-OverFlow 47515070001
2019-05-27  27315391449    cc-Loc-Voice         Transfer

和声明:

SELECT [Date],
       CallerANI,
       [47515001101],
       [Transfer]
FROM (SELECT [Date], Skills, CallerANI, CallerDNIS FROM #TempDB) AS p
PIVOT (MAX(Skills)
       FOR CallerDNIS IN ([47515001101], [Transfer])) AS pvt;

我想旋转以便结果看起来像这样:

Date            CallerANI              [47515070001]            [Transfer]
2019-05-27      273135391449           loc-SPD-CSR-OverFlow     cc-loc-Voice

Answer 1

就个人而言，我更喜欢使用交叉表而不是内置的 PIVOT 函数。我发现它更容易使用:

SELECT [Date],
       CallerANI,
       MAX(CASE WHEN V.CallerDNIS = '47515070001' THEN Skills END) AS [47515070001],
       MAX(CASE WHEN V.CallerDNIS = 'Transfer' THEN Skills END) AS [Transfer]
FROM (VALUES (CONVERT(date,'20190527'),27315391449,'loc-SPD-CSR-OverFlow','47515070001'),
             (CONVERT(date,'20190527'),27315391449,'cc-Loc-Voice','Transfer')) V([Date],CallerANI,Skills,CallerDNIS)
GROUP BY [Date],
         CallerANI;