SQL query to convert Column to Row using Pivot/Unpivot My Select Table. I tried Pivot query to get my expected result but I was struct to generate the expected result. 

WITH PersonOptions(ID,[Year], [Text], Value1, Value2) AS
(
              SELECT 'A',2000, 'X',10,2
    UNION ALL SELECT 'A', 2001, 'X',21,1
    UNION ALL SELECT 'A', 2000, 'Y',13,3
    UNION ALL SELECT 'A', 2001, 'Y',2,3
    UNION ALL SELECT 'B', 2000, 'X',0,2
    UNION ALL SELECT 'B', 2001, 'X',5,1
    UNION ALL SELECT 'B', 2000, 'Y',6,4
    UNION ALL SELECT 'B', 2001, 'Y',9,5
    UNION ALL SELECT 'C', 2000, 'X',3,5
    UNION ALL SELECT 'C', 2001, 'X',2,8
    UNION ALL SELECT 'C', 2000, 'Y',9,2
    UNION ALL SELECT 'C', 2001, 'Y',8,3
)
select *  from PersonOptions

ID  Year    Text    Value1  Value2
A   2000    X        10       2
A   2001    X        21       1
A   2000    Y        13       3
A   2001    Y        2        3
B   2000    X        0        2
B   2001    X        5        1
B   2000    Y        6        4
B   2001    Y        9        5
C   2000    X        3        5
C   2001    X        2        8
C   2000    Y        9        2
C   2001    Y        8        3


Expected Output:

               Value1          Value2   
ID  Text    2000    2001    2000    2001
A    X       10      2       21      1
A    Y       13      3       2       3
B    X        0      2       5       1
B    Y        6      4       9       5
C    X        3      5       2       8
C    Y        9      2       8       3
one way of approaching this is by doing two pivots and joining them together. see below. I also believe your expected output example is slightly wrong based on the input data, but that doesn't matter anyway.

WITH PersonOptions(ID,[Year], [Text], Value1, Value2) AS
(
          SELECT 'A',2000, 'X',10,2
UNION ALL SELECT 'A', 2001, 'X',21,1
UNION ALL SELECT 'A', 2000, 'Y',13,3
UNION ALL SELECT 'A', 2001, 'Y',2,3
UNION ALL SELECT 'B', 2000, 'X',0,2
UNION ALL SELECT 'B', 2001, 'X',5,1
UNION ALL SELECT 'B', 2000, 'Y',6,4
UNION ALL SELECT 'B', 2001, 'Y',9,5
UNION ALL SELECT 'C', 2000, 'X',3,5
UNION ALL SELECT 'C', 2001, 'X',2,8
UNION ALL SELECT 'C', 2000, 'Y',9,2
UNION ALL SELECT 'C', 2001, 'Y',8,3
)
select * INTO #t from PersonOptions

;WITH pvt1 AS
(
    SELECT pvt.ID, pvt.Text, pvt.[2000], pvt.[2001]
    FROM 
    (
        SELECT t.ID, t.Year, t.Text, t.Value1
        FROM #t t
    ) x
    PIVOT
    (
        MAX(Value1) FOR Year IN ([2000], [2001])
    ) pvt
), pvt2 AS
(
    SELECT pvt.ID, pvt.Text, pvt.[2000], pvt.[2001]
    FROM 
    (
        SELECT t.ID, t.Year, t.Text, t.Value2
        FROM #t t
    ) x
    PIVOT
    (
       MAX(Value2) FOR Year IN ([2000], [2001])
    ) pvt
)
SELECT * 
FROM pvt1
INNER JOIN pvt2 ON pvt2.ID = pvt1.ID AND pvt2.Text = pvt1.Text


DROP TABLE #t