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

Quassnoi · Accepted Answer · 2009-07-21T16:25:08.940

5

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

edited Jul 21 '09 at 16:25

answered Jul 21 '09 at 15:46

Quassnoi

413,100
91
616
614

Hi thanks for your time. I tried your code and prompt me an error: Msg 8114, Level 16, State 1, Line 1 Error converting data type nvarchar to int. Msg 473, Level 16, State 1, Line 1 The incorrect value "APPROVED" is supplied in the PIVOT operator. – Yves Jul 21 '09 at 15:56
@Yonita: your `MgtApproval` seems to be an integer, while you comparing it to a `VARCHAR` value ('APPROVED'). I've updated the post with the sample data you provided and it works. Could you please post you exact table definition? – Quassnoi Jul 21 '09 at 16:06
I have a lookup table that represents the value: MgtApproval bit Approved - true or 1 Reject - false 0r 0 Null - default value – Yves Jul 21 '09 at 16:11
MgtApproval bit (true or false with default null value) SchedId int (PK) – Yves Jul 21 '09 at 16:15
Hi Quassnoi +1: Thank you sO much for your help. You got me thinking and your code helped me very much so.: Here's my sol: SELECT 1 AS Approved, 0 AS Reject, null as NoApprovalNeeded FROM ( SELECT MgtApproval, SchedID, ROW_NUMBER() OVER (PARTITION BY mgtapproval ORDER BY SchedID) AS rn FROM LeaveRequest ) l PIVOT ( MIN(SchedId) FOR MgtApproval IN ([1], [0]) ) as pvt – Yves Jul 21 '09 at 16:34
@Yonita: glad to hear it works, but I think you still need to wrap your column names into square brackets: `SELECT [1] AS Approved, [0] AS Reject`. Otherwise, you'll be selecting the literal `1`'s and `0`'s, not the `SchedID`s – Quassnoi Jul 21 '09 at 16:43
Got it! :) I noticed my questions viewed 28x but YOU are the ONLY one that put extra effort to solve this for me. THANK YOU and bless your heart. – Yves Jul 21 '09 at 16:45

How to create a PIVOT TABLE without summing a column

1 Answers1