multiple rows to one row and multiple columns

Hi,

I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work.

Heres what i want to do:

This is the how the table is:

EmpID Designation

678 CFA 679 CFA 680 CFA 685 CFP 685 CIMA 685 IMCA

I want it to display as:

EmpID Designation1 Designation2 Designation3

678 CFA

679 CFA

680 CFA

685 CFP CIMA IMCA

could anyone provide some help on this?

Thanks

Below is one way to do it:

Code Snippet

select pt.EmpID, pt.[1] as Designation1, pt.[2] as Designation2

, pt.[3] as Designation3, pt.[4] as Designation4

from (

select t.EmpID, t.Designation

, ROW_NUMBER() OVER(PARTITION BY t.EmpID ORDER BY t.Designation) as rn

from your_table as t

) as t1

pivot (max(t1.Designation) for t1.rn in ([1], [2], [3], [4])

/* add max number of designations per emp*/

) as pt