0

This question already has an answer here:

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

marked as duplicate by hot2use, Erik Darling sql-server Sep 14 '18 at 12:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.