如何在不对列进行求和的情况下创建PIVOT TABLE

Here is my query

这是我的查询

CREATE view pivmehis WITH ENCRYPTION
as 
SELECT APPROVED, [Not Approved] as REJECT, NULL
FROM 
(SELECT MgtApproval
from LeaveRequest) l
PIVOT 
(
Sum (SchedId) --I DON'T WANT TO SUM THIS COLUMN
FOR MgtApproval IN 
([APPROVED], [REJECT], [NULL])) as pvt

Original table results:

原始表结果:

MgtApproval      SchedId
Approved           1
Reject             2
Null               3
Approved           4
Reject             5
Null               6

This is the results I would like to view:

这是我想要查看的结果:

Approved    Reject        Null
1           2           3         
4           5           6

1 个解决方案

#1

SELECT  rn, APPROVED, [Not Approved] as REJECT, NULL
FROM    (
        SELECT  MgtApproval, SchedID, ROW_NUMBER() OVER (PARTITION BY mgtapproval ORDER BY SchedID) AS rn
        FROM    LeaveRequest
        ) l
        PIVOT 
        (
        MIN(SchedId)
        FOR     MgtApproval IN 
                ([APPROVED], [Not Approved], [NULL])
        ) as pvt

A PIVOT still need any aggregation function, but this one is guaranteed to aggregate on at most one column.

PIVOT仍然需要任何聚合函数,但是这个函数保证最多聚合一列。

Here's the query on sample data which returns exactly what you want:

这是对样本数据的查询,它可以准确地返回您想要的内容:

WITH leaveRequest aS
(
SELECT 'APPROVED' AS mgtapproval, 1 AS SchedID
UNION ALL
SELECT 'Reject' AS mgtapproval, 2 AS SchedID
UNION ALL
SELECT 'NULL' AS mgtapproval, 3 AS SchedID
UNION ALL
SELECT 'APPROVED' AS mgtapproval, 4 AS SchedID
UNION ALL
SELECT 'Reject' AS mgtapproval, 5 AS SchedID
UNION ALL
SELECT 'NULL' AS mgtapproval, 6 AS SchedID
)
SELECT  APPROVED, REJECT, [NULL]
FROM    (
        SELECT  MgtApproval, SchedID, ROW_NUMBER() OVER (PARTITION BY mgtapproval ORDER BY SchedID) AS rn
        FROM    LeaveRequest
        ) l
        PIVOT 
        (
        MIN(SchedId)
        FOR     MgtApproval IN 
                ([APPROVED], [Reject], [Null])
        ) as pvt

Update:

Since you mentioned that your column is in fact a bit, you should use this syntax:

既然您提到您的列实际上有点,您应该使用以下语法:

SELECT  [1] AS approved, [0] AS reject, [-1] AS nil
FROM    (
        SELECT  COALESCE(MgtApproval, -1) AS MgtApproval, SchedID, ROW_NUMBER() OVER (PARTITION BY mgtapproval ORDER BY SchedID) AS rn
        FROM    LeaveRequest
        ) l
        PIVOT 
        (
        MIN(SchedId)
        FOR     MgtApproval IN 
                ([1], [0], [-1])
        ) as pvt

#1

SELECT  rn, APPROVED, [Not Approved] as REJECT, NULL
FROM    (
        SELECT  MgtApproval, SchedID, ROW_NUMBER() OVER (PARTITION BY mgtapproval ORDER BY SchedID) AS rn
        FROM    LeaveRequest
        ) l
        PIVOT 
        (
        MIN(SchedId)
        FOR     MgtApproval IN 
                ([APPROVED], [Not Approved], [NULL])
        ) as pvt