How to create a PIVOT TABLE without summing a column

Question

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

Answer 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.

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