I have a situation like this:
ID TYPE TIMESTAMP
1 A 22/06/2015 03:55:02
1 A 22/06/2015 03:55:05
1 B 22/06/2015 03:55:10
Of course if i try to pivot SISS gives an error telling that there is a duplicate value in the TYPE for that ID; so I thought it would be nice to have a result like this:
ID occurrence A B
1 1 22/06/2015 03:55:02 22/06/2015 03:55:10
1 2 22/06/2015 03:55:05
It would be also nice if the fist occurrence would be filled with the earlier timestamps of the events (A and B), but I think this will be done just by ordering correctly.
Can this be done?
You should be able to use ROW_NUMBER to create the "Occurrence" column and pivot the data based on Type
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY [ID], [Type] ORDER BY [TimeStamp]) Occurrence
FROM Test
) t
PIVOT
( MAX([TimeStamp])
FOR [Type] IN ([A],[B])
) p
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments