Unpivot Data Using CROSS APPLY

August 26, 2020 · Matthew

Another way to use CROSS APPLY is to unpivot a table. Unpivoting a table converts columns into rows. An example is turning a column-set into an attribute list such as taking this:

HairColor	ShirtColor	PantsColor	JacketColor	ShoeColor
Blonde	Blue	Tan	Black	Brown

And turning it into this:

Attribute	Color
HairColor	Blonde
ShirtColor	Blue
PantsColor	Tan
JacketColor	Black
ShoeColor	Brown

Sample Case

Consider a case where you have a summary table showing monthly sales for each fiscal year. For this example, I am using the following sample table:

FiscalYear	JanSales	FebSales	MarSales	AprSales	MaySales	JunSales	JulSales	AugSales	SepSales	OctSales	NovSales	DecSales
2016	1234.12	2134.21	3421.11	2431.22	1234.12	2134.21	3421.11	2431.22	1234.12	2134.21	3421.11	2431.22
2017	2134.21	3421.11	2431.22	1234.12	2134.21	3421.11	2431.22	1234.12	2134.21	3421.11	2431.22	1234.12
2018	3421.11	2431.22	1234.12	2134.21	3421.11	2431.22	1234.12	2134.21	3421.11	2431.22	1234.12	2134.21
2019	2431.22	1234.12	2134.21	3421.11	2431.22	1234.12	2134.21	3421.11	2431.22	1234.12	2134.21	3421.11
2020	1234.12	2134.21	3421.11	2431.22	1234.12	2134.21	3421.11	2431.22	NULL	NULL	NULL	NULL

DECLARE @SalesData TABLE (
	FiscalYear INT NOT NULL PRIMARY KEY
	, JanSales NUMERIC(10, 2) NULL
	, FebSales NUMERIC(10, 2) NULL
	, MarSales NUMERIC(10, 2) NULL
	, AprSales NUMERIC(10, 2) NULL
	, MaySales NUMERIC(10, 2) NULL
	, JunSales NUMERIC(10, 2) NULL
	, JulSales NUMERIC(10, 2) NULL
	, AugSales NUMERIC(10, 2) NULL
	, SepSales NUMERIC(10, 2) NULL
	, OctSales NUMERIC(10, 2) NULL
	, NovSales NUMERIC(10, 2) NULL
	, DecSales NUMERIC(10, 2) NULL
	);

INSERT @SalesData
VALUES (2016, 1234.12, 2134.21, 3421.11, 2431.22, 1234.12, 2134.21, 3421.11, 2431.22, 1234.12, 2134.21, 3421.11, 2431.22)
	, (2017, 2134.21, 3421.11, 2431.22, 1234.12, 2134.21, 3421.11, 2431.22, 1234.12, 2134.21, 3421.11, 2431.22, 1234.12)
	, (2018, 3421.11, 2431.22, 1234.12, 2134.21, 3421.11, 2431.22, 1234.12, 2134.21, 3421.11, 2431.22, 1234.12, 2134.21)
	, (2019, 2431.22, 1234.12, 2134.21, 3421.11, 2431.22, 1234.12, 2134.21, 3421.11, 2431.22, 1234.12, 2134.21, 3421.11)
	, (2020, 1234.12, 2134.21, 3421.11, 2431.22, 1234.12, 2134.21, 3421.11, 2431.22, NULL, NULL, NULL, NULL);

Now suppose you want to show the same sales data with each month as a new row. Using UNPIVOT you could use the following query:

SELECT FiscalYear
	, SalesMonth
	, Sales
FROM @SalesData
UNPIVOT (
	Sales FOR SalesMonth IN (JanSales, FebSales, MarSales, AprSales, MaySales, JunSales, JulSales, AugSales, SepSales, OctSales, NovSales, DecSales)
	) AS SalesByMonth;

This would give you the following result:

FiscalYear	SalesMonth	Sales
2016	JanSales	1234.12
2016	FebSales	2134.21
2016	MarSales	3421.11
2016	AprSales	2431.22
…	…	…

SalesMonth looks a little awkward but we can clean this up with a CASE statement like so:

SELECT FiscalYear
	, CASE SalesMonth
		WHEN 'JanSales' THEN 'January'
		WHEN 'FebSales' THEN 'February'
		WHEN 'MarSales' THEN 'March'
		WHEN 'AprSales' THEN 'April'
		WHEN 'MaySales' THEN 'May'
		WHEN 'JunSales' THEN 'June'
		WHEN 'JulSales' THEN 'July'
		WHEN 'AugSales' THEN 'August'
		WHEN 'SepSales' THEN 'September'
		WHEN 'OctSales' THEN 'October'
		WHEN 'NovSales' THEN 'November'
		WHEN 'DecSales' THEN 'December'
	END AS SalesMonth
	, Sales
FROM @SalesData
UNPIVOT (
	Sales FOR SalesMonth IN (JanSales, FebSales, MarSales, AprSales, MaySales, JunSales, JulSales, AugSales, SepSales, OctSales, NovSales, DecSales)
	) AS SalesByMonth;

Results:

FiscalYear	SalesMonth	Sales
2016	January	1234.12
2016	February	2134.21
2016	March	3421.11
2016	April	2431.22
…	…	…

That looks just the way we want it to now. Now, here’s how you could get a similar result using CROSS APPLY:

SELECT FiscalYear
	, SalesMonth
	, Sales
FROM @SalesData
CROSS APPLY (
	VALUES ('January', JanSales)
		, ('February', FebSales)
		, ('March', MarSales)
		, ('April', AprSales)
		, ('May', MaySales)
		, ('June', JunSales)
		, ('July', JulSales)
		, ('August', AugSales)
		, ('September', SepSales)
		, ('October', OctSales)
		, ('November', NovSales)
		, ('December', DecSales)
	) AS SalesByMonth(SalesMonth, Sales);

Besides looking a little nicer there is one functional difference as well. UNPIVOT will eliminate NULL values, CROSS APPLY keeps them. I could filter out the NULL values by adding “WHERE Sales IS NOT NULL” to the query but there may be cases where you want to keep the NULL values which is something UNPIVOT cannot do.

More Power

Admittedly the previous example is not the greatest way to demonstrate how much more powerful CROSS APPLY can be. There are way more powerful ways to use CROSS APPLY. For instance, what if we also wanted to generate month and quarter numbers while we are unpivoting the table? Easy!

SELECT FiscalYear
	, QuarterNumber
	, MonthNumber
	, SalesMonth
	, Sales
FROM @SalesData
CROSS APPLY (
	VALUES (1, 1, 'January', JanSales)
		, (2, 1, 'February', FebSales)
		, (3, 1, 'March', MarSales)
		, (4, 2, 'April', AprSales)
		, (5, 2, 'May', MaySales)
		, (6, 2, 'June', JunSales)
		, (7, 3, 'July', JulSales)
		, (8, 3, 'August', AugSales)
		, (9, 3, 'September', SepSales)
		, (10, 4, 'October', OctSales)
		, (11, 4, 'November', NovSales)
		, (12, 4, 'December', DecSales)
	) AS SalesByMonth(MonthNumber, QuarterNumber, SalesMonth, Sales);

Results:

FiscalYear	QuarterNumber	MonthNumber	SalesMonth	Sales
2016	1	1	January	1234.12
2016	1	2	February	2134.21
2016	1	3	March	3421.11
2016	2	4	April	2431.22
…	…	…	…	…

More Different Power

What if you wanted to unpivot two or more columns at the same time? Let’s look at the following example. Let’s say you have a flat file that has customer phone numbers that you need to import that looks like this:

CustomerID	CustomerName	Phone1Type	Phone1	Phone2Type	Phone2	Phone3Type	Phone3
1	Bob Smith	HOME	111-222-3333	CELL	222-333-4444	NULL	NULL
2	Mike Smith	WORK	333-444-5555	CELL	444-555-6666	NULL	NULL
3	Mary Smith	MOBILE	555-666-7777	OTHER	666-777-8888	WORK	777-888-9999
4	Kim Smith	HOME	123-456-7890	CELL	987-654-3210	NULL	NULL
5	Tyler Smith	HOME	111-111-1111	WORK	222-222-2222	NULL	333-333-3333

DECLARE @CustomerPhones TABLE (
	CustomerID INT NOT NULL PRIMARY KEY
	, CustomerName NVARCHAR(50) NOT NULL
	, Phone1Type VARCHAR(10) NULL
	, Phone1 VARCHAR(12) NULL
	, Phone2Type VARCHAR(10) NULL
	, Phone2 VARCHAR(12) NULL
	, Phone3Type VARCHAR(10) NULL
	, Phone3 VARCHAR(12) NULL
	);

INSERT @CustomerPhones 
VALUES (1, 'Bob Smith', 'HOME', '111-222-3333', 'CELL', '222-333-4444', NULL, NULL)
	, (2, 'Mike Smith', 'WORK', '333-444-5555', 'CELL', '444-555-6666', NULL, NULL)
	, (3, 'Mary Smith', 'MOBILE', '555-666-7777', 'OTHER', '666-777-8888', 'WORK', '777-888-9999')
	, (4, 'Kim Smith', 'HOME', '123-456-7890', 'CELL', '987-654-3210', NULL, NULL)
	, (5, 'Tyler Smith', 'HOME', '111-111-1111', 'WORK', '222-222-2222', NULL, '333-333-3333');

Let’s say you need to unpivot the phone type (PhoneType1, PhoneType2, PhoneType3) and phone number (Phone1, Phone2, Phone3) and also keep track of the ordering in the unpivot. Also easy!

SELECT CustomerID
	, CustomerName
	, PhoneOrder
	, COALESCE(PhoneType, 'OTHER') AS PhoneType
	, PhoneNumber
FROM @CustomerPhones
CROSS APPLY (
	VALUES (1, Phone1Type, Phone1)
		, (2, Phone2Type, Phone2)
		, (3, Phone3Type, Phone3)
	) AS PhonesByType(PhoneOrder, PhoneType, PhoneNumber)
WHERE PhoneNumber IS NOT NULL;

Results:

CustomerID	CustomerName	PhoneOrder	PhoneType	PhoneNumber
1	Bob Smith	1	HOME	111-222-3333
1	Bob Smith	2	CELL	222-333-4444
2	Mike Smith	1	WORK	333-444-5555
2	Mike Smith	2	CELL	444-555-6666
3	Mary Smith	1	MOBILE	555-666-7777
3	Mary Smith	2	OTHER	666-777-8888
3	Mary Smith	3	WORK	777-888-9999
4	Kim Smith	1	HOME	123-456-7890
4	Kim Smith	2	CELL	987-654-3210
5	Tyler Smith	1	HOME	111-111-1111
5	Tyler Smith	2	WORK	222-222-2222
5	Tyler Smith	3	OTHER	333-333-3333

Conclusions

The UNPIVOT expression is useful but its limited to very simple use cases. CROSS APPLY can do the same job and more.