Rows into Columns In SQL Server (May be Pivot)

Question

I need to transform Rows data into columns on a specif condition. Assume that we are in year of 2015 then It needs to fetch next years data as a new column. Please have a look at the Table A and can some one suggest how can I achieve expected result?

Table A:

---

EmpID   Year     Price     Cm       St1    St2 
03      2015      1        AB       1          0
03      2016      2        CD       0          1
04      2016      2        XY       0          1
04      2015      20       ZX       0          1 

Expected Output:

---

EmpID  Year Y1_Price Y1_Cm  Y1_St1  Y1_St2 Y2_Price Y2_Cm  Y2_St1  Y2_St2
03     2015  1       AB     1       0      2        CD     0       1
03     2016  2       CD     0       1      NULL     NULL   NULL    NULL 
04     2016  2       XY     0       1      NULL     NULL   NULL    NULL
04     2015  20      ZX     0       1      2        XY     0       1

Please help me out to write the SQL for the below scenario

Answer 1

Please over complicate the thing. I have spent around 10 mins to write this query and it is very easy to achieve this.

@Sandeep - Use below script and it will give you exactly what you are looking for. Assuming Test is your table name.

Query:

WITH CTE AS
(
SELECT EmpId, Year, Price, Cm, St1, St2, ROW_NUMBER() OVER(PARTITION BY EmpId ORDER BY Year) RNo
FROM TEST
)
SELECT T1.EmpId, T1.Year
    , T1.Price Y1_Price, T1.Cm Y1_Cm, T1.St1 Y1_St1, T1.St2 Y1_St2
    , T2.Price Y2_Price, T2.Cm Y2_Cm, T2.St1 Y2_St1, T2.St2 Y2_St2
FROM CTE T1
LEFT JOIN CTE T2 ON T1.EmpId = T2.EmpId AND T1.RNo + 1 = T2.RNo