PIVOT like functionality

Question

I have a journal table like that:

ID | Endpoint | Type | Value | TimeStamp
----------------------------------------
 1 | A        | a    | 12    | 9:00
 2 | A        | b    | 38    | 9:01
 3 | B        | a    | 65    | 9:01
 4 | B        | b    | 2     | 9:01
 5 | A        | a    | 35    | 9:04

the result should be:

 Endpoint | a  | b
----------|----|----
 A        | 35 | 38
 B        | 65 | 2

Please note that line with ID 1 is omitted because there is newer one with ID 5 in my tables. I tried to use PIVOT table but there is applied only oldest values so it is having the opposite behavior than I want.

Can It be done? My code:

SELECT [Endpoint],[a],[b]
From
(SELECT [endpoint],[type],[value] FROM [PTest]) le 
  PIVOT
  (
  MAX([value]) FOR [type] IN ([a],[b])
  )
  as pivottab

Answer 1

An example of your query for the latest values using the ROW_NUMBER window function:

SELECT [Endpoint],[a],[b]
FROM (
  SELECT [endpoint],[type],[value] FROM (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY [endpoint],[type] ORDER BY [TimeStamp] DESC) AS num
    FROM [PTest]
  ) t  
  WHERE num = 1
) le
PIVOT (MAX([value]) FOR [type] IN ([a],[b])) AS pivottab