Multi-Column Pivot & Unpivot with SQL
Just for fun: Let’s look at some interesting ways to solve a multi-column pivot or unpivot (aka crosstab). Some methods are not ANSI standard and others simply won’t work on certain RDBMSs, but we’ll play around with some methods that I came up with using SQL Server 2016.
Let’s start by creating us some data!
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 |
CREATE TABLE DBO.DAILY_AGG( YEAR_NUM INTEGER, WEEK_NUM INTEGER, MONDAY_QUANTITY INTEGER, TUESDAY_QUANTITY INTEGER, WEDNESDAY_QUANTITY INTEGER, THURSDAY_QUANTITY INTEGER, FRIDAY_QUANTITY INTEGER, MONDAY_SALES DECIMAL(18,2), TUESDAY_SALES DECIMAL(18,2), WEDNESDAY_SALES DECIMAL(18,2), THURSDAY_SALES DECIMAL(18,2), FRIDAY_SALES DECIMAL(18,2) ); INSERT INTO DBO.DAILY_AGG SELECT 2015,1,6261,9078,4741,3839,8446,351090.65,751087.02,967776.15,163461.71,201166.84; INSERT INTO DBO.DAILY_AGG SELECT 2015,2,3467,5031,9787,8893,6119,444998.27,998685.25,442258.52,99714.89,860824.63; INSERT INTO DBO.DAILY_AGG SELECT 2015,3,2604,3020,2350,4668,3653,378903.53,398006.82,709354.13,701705.89,750493.68; INSERT INTO DBO.DAILY_AGG SELECT 2015,4,4442,1299,8547,7076,5885,749663.94,80805.27,959863.86,486347.94,773219.87; INSERT INTO DBO.DAILY_AGG SELECT 2015,5,5984,9155,7547,9180,5242,773838.02,629611.93,269125.92,471155.22,756575.72; INSERT INTO DBO.DAILY_AGG SELECT 2015,6,3636,2851,1249,2680,7221,174729.8,697710.1,677991.25,813953.35,389940.51; INSERT INTO DBO.DAILY_AGG SELECT 2015,7,7175,8142,4803,3724,6810,142287.18,410442.85,66978.73,52264.06,592328.8; INSERT INTO DBO.DAILY_AGG SELECT 2015,8,9215,8614,2456,7308,1517,216363.6,297151.98,171829.46,808327.74,716100.73; INSERT INTO DBO.DAILY_AGG SELECT 2015,9,847,5540,6809,7033,4898,264955.68,751578.27,606804.49,738726.95,676071.55; INSERT INTO DBO.DAILY_AGG SELECT 2015,10,835,1285,8976,9887,6583,682418.37,195862.39,394932.4,311303.68,970859.52; INSERT INTO DBO.DAILY_AGG SELECT 2015,11,5460,8897,3436,7346,1804,587513.0,618762.63,898320.09,504073.77,69014.5; INSERT INTO DBO.DAILY_AGG SELECT 2015,12,5503,9828,692,152,4679,17376.94,509001.17,654755.01,360326.36,15127.01; INSERT INTO DBO.DAILY_AGG SELECT 2015,13,263,2297,1754,2052,3157,968768.54,943142.94,389485.94,286475.66,124710.4; INSERT INTO DBO.DAILY_AGG SELECT 2015,14,5154,3614,4926,6974,4480,845260.62,992490.22,681413.05,666515.0,613213.78; INSERT INTO DBO.DAILY_AGG SELECT 2015,15,5434,3961,1372,3723,2577,666152.0,461108.45,944670.66,5120.01,334423.71; INSERT INTO DBO.DAILY_AGG SELECT 2015,16,588,1308,7452,6349,9195,426085.26,866878.76,748818.01,47963.51,255202.09; INSERT INTO DBO.DAILY_AGG SELECT 2015,17,8472,7988,8711,5972,9136,597639.16,971710.28,206623.88,166526.64,420527.11; INSERT INTO DBO.DAILY_AGG SELECT 2015,18,8431,6971,3165,1512,8633,460411.4,623423.3,807750.28,782701.12,431187.05; INSERT INTO DBO.DAILY_AGG SELECT 2015,19,1119,8891,5454,5389,3563,930543.9,547265.18,724235.02,834010.63,566756.52; INSERT INTO DBO.DAILY_AGG SELECT 2015,20,8014,3512,9391,3962,2570,746711.13,649359.23,852311.09,615616.65,686796.38; INSERT INTO DBO.DAILY_AGG SELECT 2015,21,5130,3367,5604,407,6664,349586.0,878799.95,950162.38,250325.7,898556.88; INSERT INTO DBO.DAILY_AGG SELECT 2015,22,4714,6200,7126,1878,4438,384880.45,445045.66,428351.27,928976.0,34906.51; INSERT INTO DBO.DAILY_AGG SELECT 2015,23,3219,2101,4139,1184,8778,942992.14,36183.84,411596.34,191260.04,889081.15; INSERT INTO DBO.DAILY_AGG SELECT 2015,24,5067,5017,3,1642,496,840415.5,792978.37,547579.32,233123.89,341009.37; INSERT INTO DBO.DAILY_AGG SELECT 2015,25,6693,5921,7380,4737,664,158716.26,910487.55,287565.74,133276.57,400690.0; INSERT INTO DBO.DAILY_AGG SELECT 2015,26,4586,4665,9479,9533,9513,780227.26,886790.29,883459.4,711781.03,868881.49; INSERT INTO DBO.DAILY_AGG SELECT 2015,27,2711,34,2182,4664,6381,988571.63,1197.48,617976.96,64872.17,218419.25; INSERT INTO DBO.DAILY_AGG SELECT 2015,28,247,7007,4023,9902,4648,466945.33,464205.73,384160.91,340174.49,316113.52; INSERT INTO DBO.DAILY_AGG SELECT 2015,29,3021,1919,6748,2661,4407,409901.26,979782.0,338740.9,907911.92,772788.76; INSERT INTO DBO.DAILY_AGG SELECT 2015,30,7539,948,7861,8869,6366,463628.46,613349.64,435065.73,650931.87,86693.4; INSERT INTO DBO.DAILY_AGG SELECT 2015,31,4951,3858,1649,347,6743,459423.64,691464.26,387391.07,141927.05,476826.53; INSERT INTO DBO.DAILY_AGG SELECT 2015,32,931,9149,4353,2542,2330,178852.94,355147.52,602655.35,360555.81,784053.76; INSERT INTO DBO.DAILY_AGG SELECT 2015,33,3382,7000,2218,4905,4534,628525.23,566007.86,464898.77,550708.32,275237.26; INSERT INTO DBO.DAILY_AGG SELECT 2015,34,4408,8296,3755,8586,5643,762315.02,350363.61,650356.31,534122.28,3493.2; INSERT INTO DBO.DAILY_AGG SELECT 2015,35,5885,4629,8539,4646,5444,534313.62,770307.43,289338.98,199166.52,416103.0; INSERT INTO DBO.DAILY_AGG SELECT 2015,36,4060,2135,9308,942,9148,842533.35,543083.96,951942.09,280702.65,107846.55; INSERT INTO DBO.DAILY_AGG SELECT 2015,37,6111,6722,3402,6866,1203,220703.78,34684.52,956999.33,4660.38,284966.57; INSERT INTO DBO.DAILY_AGG SELECT 2015,38,9063,1202,959,3075,5946,623307.65,797419.96,902659.46,502527.09,449732.69; INSERT INTO DBO.DAILY_AGG SELECT 2015,39,9320,651,4117,8559,5476,131408.53,155036.05,515878.68,756276.12,263551.78; INSERT INTO DBO.DAILY_AGG SELECT 2015,40,7639,8267,6306,2654,6702,128958.86,184047.85,597756.4,298637.19,767701.0; INSERT INTO DBO.DAILY_AGG SELECT 2015,41,4198,2735,4549,5972,1851,328173.57,13976.87,324736.51,713461.24,179497.7; INSERT INTO DBO.DAILY_AGG SELECT 2015,42,3827,6259,2619,2784,1989,376537.33,940336.25,568458.37,46234.35,948530.96; INSERT INTO DBO.DAILY_AGG SELECT 2015,43,5856,5660,3970,4030,5997,40832.74,428135.57,652162.08,321434.63,713051.64; INSERT INTO DBO.DAILY_AGG SELECT 2015,44,5115,3773,3928,3037,5471,981873.81,746618.66,670510.99,443988.57,261115.0; INSERT INTO DBO.DAILY_AGG SELECT 2015,45,3731,8807,4749,1864,2496,604484.33,633088.33,654052.92,169276.65,231993.48; INSERT INTO DBO.DAILY_AGG SELECT 2015,46,1936,6176,1469,7701,7743,78925.85,497770.14,272026.75,831380.56,685635.1; INSERT INTO DBO.DAILY_AGG SELECT 2015,47,5300,6387,7514,9296,7897,209845.77,356156.52,889882.8,357018.12,437544.42; INSERT INTO DBO.DAILY_AGG SELECT 2015,48,6761,2494,319,402,6178,332398.31,961656.16,535489.12,920835.46,253685.89; INSERT INTO DBO.DAILY_AGG SELECT 2015,49,2279,8889,2823,4057,8616,942490.12,104964.19,523539.91,324513.77,923935.46; INSERT INTO DBO.DAILY_AGG SELECT 2015,50,9134,3488,222,8317,498,165522.47,56232.76,863384.9,837205.27,768069.99; INSERT INTO DBO.DAILY_AGG SELECT 2015,51,4225,926,2394,1356,2916,906497.0,943671.96,600121.26,980164.71,64897.74; INSERT INTO DBO.DAILY_AGG SELECT 2015,52,7481,8178,1747,3658,9640,709851.17,158057.83,443409.41,193497.92,151910.75; INSERT INTO DBO.DAILY_AGG SELECT 2016,1,798,6122,918,273,2790,443275.0,272216.71,287990.87,667203.27,29553.63; INSERT INTO DBO.DAILY_AGG SELECT 2016,2,5891,3113,1616,5041,3795,701683.16,589908.39,718425.38,726635.0,615079.23; INSERT INTO DBO.DAILY_AGG SELECT 2016,3,9476,4108,7301,9504,6587,827054.16,928922.31,838897.33,886962.04,186515.22; INSERT INTO DBO.DAILY_AGG SELECT 2016,4,5635,5039,9927,9914,1532,664802.52,620755.41,880342.45,75041.0,585764.06; INSERT INTO DBO.DAILY_AGG SELECT 2016,5,1137,5904,8218,997,9542,983820.0,69312.0,680217.44,202974.86,363793.16; INSERT INTO DBO.DAILY_AGG SELECT 2016,6,8369,5383,6419,271,9939,512332.27,320730.09,409142.22,388996.9,631104.05; INSERT INTO DBO.DAILY_AGG SELECT 2016,7,3243,7194,5025,7005,8743,199374.16,401162.4,767081.0,267117.57,65315.06; INSERT INTO DBO.DAILY_AGG SELECT 2016,8,6998,7891,4951,6014,6686,180355.32,950004.25,519856.57,923898.95,823069.87; INSERT INTO DBO.DAILY_AGG SELECT 2016,9,3277,2943,9564,2179,4167,917570.69,978309.55,858307.33,244390.51,25753.47; INSERT INTO DBO.DAILY_AGG SELECT 2016,10,2213,6037,4607,97,894,464940.46,701973.07,646434.3,875895.89,712437.0; INSERT INTO DBO.DAILY_AGG SELECT 2016,11,9641,5623,7079,164,2310,458176.37,560991.0,996001.04,294398.03,242121.12; INSERT INTO DBO.DAILY_AGG SELECT 2016,12,1061,8651,7158,9646,3305,877190.54,991537.23,688581.18,546948.82,121968.48; INSERT INTO DBO.DAILY_AGG SELECT 2016,13,123,5813,8032,1984,2316,627697.28,132588.75,201107.84,754875.72,427703.98; INSERT INTO DBO.DAILY_AGG SELECT 2016,14,2484,1309,3129,1221,4348,95241.91,437838.29,864734.76,605276.6,694726.0; INSERT INTO DBO.DAILY_AGG SELECT 2016,15,756,4251,1312,8815,9606,135926.62,406472.9,647572.97,959233.99,217929.33; INSERT INTO DBO.DAILY_AGG SELECT 2016,16,4673,3510,216,5419,9893,913881.0,366301.13,570623.08,970141.24,601453.85; INSERT INTO DBO.DAILY_AGG SELECT 2016,17,9321,7360,4795,804,7998,528629.59,663434.02,682590.02,696565.34,810236.26; INSERT INTO DBO.DAILY_AGG SELECT 2016,18,9613,5232,3006,3547,2526,350621.65,986542.07,870802.57,710652.2,160471.56; INSERT INTO DBO.DAILY_AGG SELECT 2016,19,7042,6089,541,4345,5532,762012.0,173846.2,98804.53,24135.87,838054.8; INSERT INTO DBO.DAILY_AGG SELECT 2016,20,822,130,4020,8816,2851,647939.62,739749.5,633188.44,854459.29,972372.0; INSERT INTO DBO.DAILY_AGG SELECT 2016,21,9199,7949,8156,4496,5067,975734.0,742984.03,823956.93,370816.59,591774.69; INSERT INTO DBO.DAILY_AGG SELECT 2016,22,3688,4931,7470,5935,4232,397579.5,739765.59,489431.93,226522.33,790741.79; INSERT INTO DBO.DAILY_AGG SELECT 2016,23,4409,64,9362,9846,6413,92416.7,651520.35,291378.09,800602.05,347024.55; INSERT INTO DBO.DAILY_AGG SELECT 2016,24,6018,7946,7476,4622,7439,23598.14,246695.85,188595.28,507228.28,384642.83; INSERT INTO DBO.DAILY_AGG SELECT 2016,25,3542,1867,1922,1306,5345,686856.6,374255.32,821139.03,944169.7,373942.17; INSERT INTO DBO.DAILY_AGG SELECT 2016,26,7543,6213,3428,2277,4464,536575.0,612766.02,548132.54,130708.05,674973.23; INSERT INTO DBO.DAILY_AGG SELECT 2016,27,5041,7606,6969,7142,607,962976.55,282656.08,437305.28,42000.35,590318.35; INSERT INTO DBO.DAILY_AGG SELECT 2016,28,573,5951,2113,2358,4902,295259.28,56888.44,371455.41,506180.77,804779.99; INSERT INTO DBO.DAILY_AGG SELECT 2016,29,5917,5315,7975,4612,9868,606829.94,151395.33,898521.28,351272.73,386743.88; INSERT INTO DBO.DAILY_AGG SELECT 2016,30,3708,8870,389,5115,1151,521051.98,141538.63,356277.15,746837.57,330231.93; INSERT INTO DBO.DAILY_AGG SELECT 2016,31,7427,3550,2702,8753,5827,589534.83,259709.38,589938.38,791113.0,763313.59; INSERT INTO DBO.DAILY_AGG SELECT 2016,32,1586,3114,9987,8137,9733,999595.71,762200.82,468026.54,153849.12,367347.26; INSERT INTO DBO.DAILY_AGG SELECT 2016,33,3158,26,5208,1597,1309,388223.67,394496.87,117554.24,42711.0,671741.15; INSERT INTO DBO.DAILY_AGG SELECT 2016,34,8853,7091,2646,2404,9253,45437.14,339164.54,51896.09,433689.61,777044.09; INSERT INTO DBO.DAILY_AGG SELECT 2016,35,7293,7862,1011,8374,2772,418648.13,330580.14,769028.19,366230.0,527730.04; INSERT INTO DBO.DAILY_AGG SELECT 2016,36,4222,7950,1997,9536,3746,624875.0,656718.33,970193.66,452335.42,200481.76; INSERT INTO DBO.DAILY_AGG SELECT 2016,37,1969,4312,5765,7356,4623,837379.14,959384.8,172212.7,668069.95,58106.6; INSERT INTO DBO.DAILY_AGG SELECT 2016,38,8850,9244,5049,2992,4786,336428.19,813474.53,316519.16,922269.9,252036.67; INSERT INTO DBO.DAILY_AGG SELECT 2016,39,2142,2686,3329,8244,204,967614.3,896304.0,188665.71,543936.34,829623.36; INSERT INTO DBO.DAILY_AGG SELECT 2016,40,3679,3942,8878,963,7356,797921.6,171607.88,168163.45,751920.0,129091.33; INSERT INTO DBO.DAILY_AGG SELECT 2016,41,241,1425,1720,6966,9097,122060.11,951125.0,36599.86,675995.75,963695.61; INSERT INTO DBO.DAILY_AGG SELECT 2016,42,3519,1090,58,6050,3191,886594.51,937643.6,982215.91,6334.91,37762.79; INSERT INTO DBO.DAILY_AGG SELECT 2016,43,6750,6330,8319,9566,1813,269598.43,739172.54,557872.43,872308.49,935208.79; INSERT INTO DBO.DAILY_AGG SELECT 2016,44,7878,4931,6534,8567,2376,92879.18,991749.62,203112.6,957657.41,629232.81; INSERT INTO DBO.DAILY_AGG SELECT 2016,45,7033,2312,9709,2376,4043,465515.24,31646.16,186986.95,232976.27,296373.98; INSERT INTO DBO.DAILY_AGG SELECT 2016,46,5576,8549,312,1923,8048,15264.47,371823.11,458579.65,652661.3,677097.5; INSERT INTO DBO.DAILY_AGG SELECT 2016,47,4764,5708,2899,5432,1855,601491.25,781429.28,330448.08,921769.62,454599.4; INSERT INTO DBO.DAILY_AGG SELECT 2016,48,9964,383,420,8167,1046,423098.77,627235.27,288698.23,438153.98,212502.11; INSERT INTO DBO.DAILY_AGG SELECT 2016,49,5793,2992,628,210,6781,499106.05,265696.49,364602.43,205933.84,684141.93; INSERT INTO DBO.DAILY_AGG SELECT 2016,50,9036,61,7379,1791,9405,136110.67,306949.32,963818.98,600166.67,81391.67; INSERT INTO DBO.DAILY_AGG SELECT 2016,51,2824,6744,3309,4056,6708,339939.25,103745.55,379283.77,413282.58,659985.35; INSERT INTO DBO.DAILY_AGG SELECT 2016,52,8830,1149,6527,9753,7351,779276.84,753197.59,909140.42,634806.15,956973.97; |
The data used in this demo was generated using Mockaroo.com
Thanks, Mockaroo!
You will notice that the table we just created, populated with mock data, has several denormalized columns from a poorly architected attempt to create a daily aggregate from, what appears to be, weekly data. The initial developer decided it was easier to create a column for each day of the week for the sales amount and sales quantity. It’s our job now to unpivot that data back into rows so that we can do proper roll-ups and analysis.
Multi-Column unpivot using UNPIVOT clause:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT YEAR_NUM AS YEAR ,WEEK_NUM AS WEEK_NUMBER ,SUBSTRING(QUANTITY_DAY,1,CHARINDEX('_',QUANTITY_DAY)-1) AS DAY ,QUANTITY AS QUANTITY ,SALES AS SALES FROM DBO.DAILY_AGG D UNPIVOT ( QUANTITY FOR QUANTITY_DAY IN (MONDAY_QUANTITY, TUESDAY_QUANTITY, WEDNESDAY_QUANTITY, THURSDAY_QUANTITY, FRIDAY_QUANTITY) ) Q UNPIVOT ( SALES FOR SALES_DAY IN (MONDAY_SALES, TUESDAY_SALES, WEDNESDAY_SALES, THURSDAY_SALES, FRIDAY_SALES) ) S WHERE LEFT(QUANTITY_DAY, 2) = LEFT(SALES_DAY,2) ORDER BY 1,2; |
This is the best, modern way to properly pivot and unpivot data in SQL. This example is a multi-column pivot technique that can pivot or unpivot data for more than one column at a time. The trick here is the use of the WHERE clause to “join” the data back together again. If you just want to manipulate a single column of data then you don’t need the where clause at all.
Multi-Column unpivot using CROSS APPLY clause with VALUES:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT s.YEAR_NUM AS YEAR ,s.WEEK_NUM AS WEEK_NUMBER ,d.DAY ,d.QUANTITY ,d.SALES FROM DBO.DAILY_AGG s CROSS APPLY ( VALUES ('MONDAY', MONDAY_QUANTITY, MONDAY_SALES) ,('TUESDAY', TUESDAY_QUANTITY, TUESDAY_SALES) ,('WEDNESDAY', WEDNESDAY_QUANTITY, WEDNESDAY_SALES) ,('THURSDAY', THURSDAY_QUANTITY, THURSDAY_SALES) ,('FRIDAY', FRIDAY_QUANTITY, FRIDAY_SALES) ) d (DAY, QUANTITY, SALES) ORDER BY 1,2; |
This is another great technique to pivot or unpviot data. By using VALUES within a CROSS APPLY, you can achieve a multi-column pivot by simply adding more values that you wish to pivot. This, however, is only available in some modern RDBMSs.
Multi-Column unpivot using UNION ALL:
|
0 1 2 3 4 5 6 7 |
SELECT YEAR_NUM AS YEAR, WEEK_NUM AS WEEK_NUMBER, 'MONDAY' AS DAY, MONDAY_QUANTITY, MONDAY_SALES FROM DBO.DAILY_AGG UNION ALL SELECT YEAR_NUM AS YEAR, WEEK_NUM AS WEEK_NUMBER, 'TUESDAY' AS DAY, TUESDAY_QUANTITY, TUESDAY_SALES FROM DBO.DAILY_AGG UNION ALL SELECT YEAR_NUM AS YEAR, WEEK_NUM AS WEEK_NUMBER, 'WEDNESDAY' AS DAY, WEDNESDAY_QUANTITY, WEDNESDAY_SALES FROM DBO.DAILY_AGG UNION ALL SELECT YEAR_NUM AS YEAR, WEEK_NUM AS WEEK_NUMBER, 'THURSDAY' AS DAY, THURSDAY_QUANTITY, THURSDAY_SALES FROM DBO.DAILY_AGG UNION ALL SELECT YEAR_NUM AS YEAR, WEEK_NUM AS WEEK_NUMBER, 'FRIDAY' AS DAY, FRIDAY_QUANTITY, FRIDAY_SALES FROM DBO.DAILY_AGG ORDER BY 1,2; |
This method, while carrying a heavy performance hit, is the defacto method to pivot & unpivot data. Older versions of RDBMSs all support this method and could be considered ANSI standard, although understand that it’s the use of UNION ALL that makes it that way.
There are other methods that are specific to your database but they’re not worth mentioning here due to the lack of generic usability. Everything you’ve seen in this post will work with any modern RDBMS and in one case will work with even the oldest production systems.
Can you come up with any other modern, yet generic techniques?