[EDIT #1]
There is not possible to achieve that using
pivot(s)[
^], because
aggregate function(s)[
^] can be used only on numeric data.
[EDIT]
Try this:
DECLARE @emp TABLE(EMPID INT, VehicalID NVARCHAR(30))
INSERT INTO @emp (EMPID, VehicalID)
VALUES(1, 'AB-1')
INSERT INTO @emp (EMPID, VehicalID)
VALUES(1, 'CD-1')
INSERT INTO @emp (EMPID, VehicalID)
VALUES(1, 'DE-1')
INSERT INTO @emp (EMPID, VehicalID)
VALUES(2, 'XY-1')
INSERT INTO @emp (EMPID, VehicalID)
VALUES(2, 'PQ-2')
DECLARE @tmp TABLE (CID INT IDENTITY(1,1), EMPID INT, VehNo INT, VehicalID NVARCHAR(30))
INSERT INTO @tmp (EMPID, VehNo, VehicalID)
SELECT EMPID, ROW_NUMBER() OVER(PARTITION BY EMPID ORDER BY VehicalID) AS VehNo, VehicalID
FROM @emp
SELECT DISTINCT total.EMPID, t1.VehicalID AS Veh1, t2.VehicalID AS Veh2, t3.VehicalID AS Veh3
FROM @tmp AS total
LEFT JOIN @tmp AS t1 ON total.EMPID = t1.EMPID AND t1.VehNo = 1
LEFT JOIN @tmp AS t2 ON total.EMPID = t2.EMPID AND t2.VehNo = 2
LEFT JOIN @tmp AS t3 ON total.EMPID = t3.EMPID AND t3.VehNo = 3
Returns:
EMPID Veh1 Veh2 Veh3
1 AB-1 CD-1 DE-1
2 PQ-2 XY-1 NULL