6

I'm needing to unpivot the below table so that the output looks as shown in below image.

enter image description here

Does this require me to perform an UNPIVOT twice on the dataset or can I accomplish my expected output by using UNPIVOT once and specifying all the available Month and Value columns?

Should my script look something like the following to accomplish what I need?

 Select ID, Name, Age, Gender,Month,Value
    FROM
    (Select ID, Name, Age, Gender,Month1,Month2,Month3,Month4,Value1,Value2,Value3,Value4
    FROM MyTable
    ) as cp
    UNPIVOT 
    (
      Month FOR Months IN (Month1, Month2, Month3,Month4),
      Value for Values IN (Value1,Value2,Value3,Value4)
    ) AS up;
0
9

What you want to do is a cross apply instead of an unpivot. That allows multiple columns to be unpivoted whereas UNPIVOT only allows a single column to be unpivoted.

Something like this:

CREATE TABLE dbo.Struc
(
    ID int NOT NULL
    , Person nvarchar(30) NOT NULL
    , Age int NOT NULL
    , Gender char(1) NOT NULL
    , Month1 int NOT NULL
    , Value1 int NOT NULL
    , Month2 int NOT NULL
    , Value2 int NOT NULL
);

INSERT INTO dbo.Struc (ID, Person, Age, Gender, Month1, Value1, Month2, Value2)
VALUES (1, 'Jane', 20, 'F', 201507, 1, 201508, 0)
    , (2, 'John', 30, 'M', 201507, 0, 201508, 1);

SELECT s.ID
    , s.Person
    , s.Age
    , s.Gender
    , v.Month
    , v.Value
FROM dbo.Struc s
CROSS APPLY (VALUES 
                  (Month1, Value1)
                , (Month2, Value2)
            ) v(Month, Value);

The results:

+----+--------+-----+--------+--------+-------+
| ID | Person | Age | Gender | Month  | Value |
+----+--------+-----+--------+--------+-------+
|  1 | Jane   |  20 | F      | 201507 |     1 |
|  1 | Jane   |  20 | F      | 201508 |     0 |
|  2 | John   |  30 | M      | 201507 |     0 |
|  2 | John   |  30 | M      | 201508 |     1 |
+----+--------+-----+--------+--------+-------+
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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