patternsqlModerate
Double Unpivot?
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?
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
Something like this:
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 |
+----+--------+-----+--------+--------+-------+
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.