HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Double Unpivot?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
doubleunpivotstackoverflow

Problem

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

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;

Solution

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 |
+----+--------+-----+--------+--------+-------+

Code Snippets

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);

Context

StackExchange Database Administrators Q#163815, answer score: 12

Revisions (0)

No revisions yet.