Question

我有以下表格:

WITH nCTE AS
(
   SELECT *
   FROM 
   TableA X INNER JOIN TableB Y
   ON X.ID = Y.ID
)
SELECT *
FROM
(
   SELECT ...
   FROM nCTE
)
WHERE ...
GROUP BY ...
ORDER BY ...

这种质疑的结果就是:

ID   Name   Badge   Type   Value
1   John    Blue    Pre    10
1   John    Blue    Post   20
1   John    Blue    Cur    30
1   John    Green   Pre    50
1   John    Green   Post   30
1   John    Green   Cur    20

我试图以以下形式取得结果:

ID   Name   Badge   Pre   Cur   Post
1    John   Blue    10    20    30
2    John   Green   50    30    20

由于我正在使用一个联合小组,我不相信我能直接这样做,或者我应该多次自我参与,以达到这一目的(这只会使一切都看得很长、ug和复杂)。是否有实现这一目标的好办法?

www.un.org/Depts/DGACM/index_spanish.htm UPDATE: Trying this Return aNUL Value

WITH nCTE AS
(
   SELECT *
   FROM 
   TableA X INNER JOIN TableB Y
   ON X.ID = Y.ID
)
SELECT ID, Name, Badge, [0] AS  Pre , [1] AS  Cur , [2] AS  Post 
FROM
(
    SELECT *
    FROM
    (
       SELECT ...
       FROM nCTE
    )
    WHERE ...
    GROUP BY ...
) Y
PIVOT
(
   MAX(Value)
   FOR Type IN ([0], [1], [2])
) AS PivotTable
ORDER BY ...

在问询本身奏效时,它返回如下:

ID   Name   Badge   Pre     Cur     Post
1    John   Blue    NULL    NULL    NULL
2    John   Green   NULL    NULL    NULL

任何建议为什么会发生?

Answer 1

我的祝福! 投票需要实际价值。我对MSDN的榜样视而不见。无论如何,以下工作:

WITH nCTE AS
(
   SELECT *
   FROM 
   TableA X INNER JOIN TableB Y
   ON X.ID = Y.ID
)
SELECT ID, Name, Badge, [Pre] AS  Pre , [Cur] AS  Cur , [Post] AS  Post 
FROM
(
    SELECT *
    FROM
    (
       SELECT ...
       FROM nCTE
    )
    WHERE ...
    GROUP BY ...
) Y
PIVOT
(
   MAX(Value)
   FOR Type IN ([Pre], [Cur], [Post])
) AS PivotTable
ORDER BY ...

友情链接