PIVOT for getting MAX dates of each version

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 389
Create table SiteDtails (SiteNo int, SubNo varchar(10), MV varchar(5), Date date)
INSERT INTO SiteDtails
VALUES(001, 'SUB-028', 'V1', '2007-03-11'),
(001, 'SUB-028', 'V1', '2007-03-15'),
(001, 'SUB-028', 'V1', '2007-03-19'),
(001, 'SUB-028', 'V2', '2007-05-21'),
(001, 'SUB-028', 'V3', '2007-06-22'),
(001, 'SUB-028', 'V4', '2007-07-11'),
(001, 'SUB-028', 'V4', '2007-07-17'),
(001, 'SUB-028', 'V4', '2007-07-21'),
(001, 'SUB-028', 'V4', '2007-07-29'),
(001, 'SUB-028', 'V5', '2007-08-05'),
(001, 'SUB-028', 'V6', '2007-09-28'),
(002, 'SUB-010', 'V1', '2007-04-22'),
(002, 'SUB-010', 'V2', '2007-05-22'),
(002, 'SUB-010', 'V3', '2007-06-05'),
(002, 'SUB-010', 'V3', '2007-07-05')


SELECT SiteNO, SubNo, [V1] ,[V2] ,[V3] ,[V4] ,[V5] ,[V6]
FROM
(
SELECT SiteNo, SubNo, MV, DATE
FROM SiteDtails
) p
PIVOT
( MAX( date) FOR MV IN ( [V1] ,[V2] ,[V3] ,[V4] ,[V5] ,[V6] ) ) AS pvt

/* OUTPUT
SiteNO SubNo V1 V2 V3 V4 V5 V6
2 SUB-010 2007-04-22 2007-05-22 2007-07-05 NULL NULL NULL
1 SUB-028 2007-03-19 2007-05-21 2007-06-22 2007-07-29 2007-08-05 2007-09-28
*/

Comments or Responses

Login to post response