PIVOT Query gives NULL values

Question

My actual table is below:

   DATE       Server           DURATION_
2015-04-01    BANGLR           01:11:00.0000000
2015-04-01    ERRAMANJIL       00:19:00.0000000
2015-04-01    THANE            00:42:00.0000000
2015-04-02    BANGLR           01:27:00.0000000
2015-04-02    ERRAMANJIL       00:20:00.0000000
2015-04-02    THANE            00:33:00.0000000

I am using the below PIVOT query:

SELECT DATE, BANGLR, THANE, ERRAMANJIL FROM abrlmis_hmdmtracking
PIVOT (MAX(DURATION_) for [Server] IN (BANGLR,THANE,ERRAMANJIL)) AS [SERVER]
WHERE [DATE] IS NOT NULL 
GROUP BY DATE, BANGLR,THANE,ERRAMANJIL

But it gives the result with null values like below:

   DATE      BANGLR              THANE                   ERRAMANJIL
2015-04-01   01:11:00.0000000    NULL                    NULL
2015-04-01   NULL                NULL                    NULL
2015-04-01   NULL                NULL                    00:19:00.0000000
2015-04-01   NULL                00:42:00.0000000        NULL
2015-04-02   01:27:00.0000000    NULL                    NULL
2015-04-02   NULL                NULL                    NULL
2015-04-02   NULL                NULL                    00:20:00.0000000
2015-04-02   NULL                00:33:00.0000000        NULL

But my desired result would be like this:

   DATE      BANGLR             THANE              ERRAMANJIL
2015-04-01   01:11:00.0000000   00:42:00.0000000   00:19:00.0000000
2015-04-02   01:27:00.0000000   00:33:00.0000000   00:20:00.0000000

How to achieve this?

Answer 1

As you can see from the T-SQL statement below your query is working fine with the provided data:

DECLARE @DataSource TABLE
(
    [DATE] DATE
   ,[Server] VARCHAR(12)
   ,[DURATION_] VARCHAR(18)
);

INSERT INTO @DataSource ([DATE], [Server], [DURATION_])
VALUES ('2015-04-01', 'BANGLR', '01:11:00.0000000')
      ,('2015-04-01', 'ERRAMANJIL', '00:19:00.0000000')
      ,('2015-04-01', 'THANE', ' 00:42:00.0000000')
      ,('2015-04-02', 'BANGLR', '01:27:00.0000000')
      ,('2015-04-02', 'ERRAMANJIL', '00:20:00.0000000')
      ,('2015-04-02', 'THANE', '00:33:00.0000000');

SELECT TOP 8 DATE, BANGLR, THANE, ERRAMANJIL 
FROM @DataSource
PIVOT 
(
    MAX(DURATION_) FOR [Server] IN (BANGLR,THANE,ERRAMANJIL)
) AS [SERVER]
WHERE [DATE] IS NOT NULL 
GROUP BY DATE, BANGLR,THANE,ERRAMANJIL;

So, I believe you are extracting more columns then you have shown to us (and some of them are unique). To solve this you can simple do this:

SELECT TOP 8 DATE, BANGLR, THANE, ERRAMANJIL 
FROM 
(
    SELECT [DATE] 
          ,DURATION_
          ,[Server]
    FROM @DataSource
    WHERE [DATE] IS NOT NULL 
) DS
PIVOT 
(
    MAX(DURATION_) FOR [Server] IN (BANGLR,THANE,ERRAMANJIL)
) AS [SERVER];