{"id":727,"date":"2009-11-12T00:00:00","date_gmt":"2009-11-12T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/pivoting-un-pivoting-and-aggregating-a-quick-spin-around-the-block\/"},"modified":"2021-09-29T16:22:03","modified_gmt":"2021-09-29T16:22:03","slug":"pivoting-un-pivoting-and-aggregating-a-quick-spin-around-the-block","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/pivoting-un-pivoting-and-aggregating-a-quick-spin-around-the-block\/","title":{"rendered":"Pivoting, Un-pivoting and Aggregating: A Quick Spin Around the Block"},"content":{"rendered":"
\n

In this article, I’ll be showing a few techniques in pivoting and un-pivoting data, and showing you how the use of the CUBE or ROLLUP operator can be more efficient way of generating reports.<\/p>\n

Why use Aggregate Tables?<\/h1>\n

I’ve always found it puzzling that Data Warehousing systems want to re-aggregate data over, and over again, especially if the data is time-based.\u00a0
\nImagine you have to report on a simple ledger. Ledgers, like most accounting entities, are based on written entries in leather-bound books. You start at the beginning, and add entries over time, but nothing is ever erased.\u00a0 Once an accounting for a period of time is done and balanced, then it is closed. Why then do we recalculate our trading figures, or past commercial activity, when no legitimate retrospective modifications are possible or valid?\u00a0 It is therefore bewildering to find reporting packages continually aggregating totals for particular time-periods from the raw data every time a report is run. Obviously, there are times when it is necessary to do so, but even when it is, it is possible to prevent a complete trawl of possibly millions of rows of data.<\/p>\n

Business reports should always go to aggregation tables for their information wherever possible. This is especially true on production systems where the amount of data gets serious. An aggregation table often looks wrong to a Database Theorist because it looks suspiciously un-normalized.\u00a0 This is missing the point.<\/p>\n

Aggregation operators<\/h1>\n

I find The ROLLUP, CUBE, and GROUPING SETS operators, which are extensions of the GROUP BY clause, to be useful for simplifying aggregation. Queries that contain CUBE and ROLLUP perform some of the same calculations as you’ll find in OLAP applications. The CUBE operator generates a result set that can be used for a variety of cross tabulation reports. A ROLLUP operation can calculate the equivalent of an OLAP dimension or hierarchy. They might, at first glance, seem slightly pointless until you start doing reports on large quantities of data.<\/p>\n

The ROLLUP operator is perfect for any reports on data that have column totals. It means that you save a UNION ALL and an explicit second pass over the data<\/p>\n

A CUBE comes into its own if you have several reports to produce from one source of data. Once you have performed the initial aggregation, and you have chosen your GROUP BY elements well, then you never have to access the original data, in order to slice and dice the data. All sorts of reports can be generated from the result of the CUBE just so long as you have specified the grouping properly. The cube performs all the possible permutations of the aggregation and adds them to the result.<\/p>\n

You can, of course, generate the same result set when you use UNION ALL to combine single grouping queries; but the advantages of performing the query aggregation once really kicks in once you have millions of rows in your raw data tables. Using ROLLUP, CUBE, and GROUPING SETS is more efficient, though the SQL Server optimizer currently \u00a0translates a CUBE into several ROLLUPs which operate on a common sub-expression spool, so it is not as efficient as a single-pass through the data.<\/p>\n

The GROUPING SETS operator was introduced in SQL Server 2008 in order to conform with Ansi SQL 2006. The old syntax, WITH ROLLUP and WITH CUBE, which were functions that were intended for Data Warehousing, are found in SQL Server, and Sybase only. The Oracle version works like Ansi SQL 2006.\u00a0 The GROUPING SETS operator can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator. However, you can use GROUPING SETS to refine your query in order to specify only the groupings that you actually want. You can even use GROUPING SETS with ROLLUP and CUBE, but you are liable to get duplicate groupings, and you will probably make your head \u00a0spin<\/p>\n

A Simple Example<\/h1>\n

In this article, I’d like to do a round trip from published data, to a normalized table, through to an aggregation table and out to some reports. For our example, We want to do an analysis from external data. Just to make this more interesting, we’ll choose the oil production figures (thousand barrels per day) \u00a0for the past nine years. These are available from Energy Information Administration (Oct 2008) from https:\/\/www.eia.gov\/petroleum\/supply\/weekly\/<\/a>.\u00a0 The only downside is that it is not a multi-million row table, the sort of size where the use of an aggregation table makes solid sense.<\/p>\n

We pick up four excel files. They are pivot-table reports done in the format…<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\u00a0<\/td>\n\n

Algeria<\/b><\/p>\n<\/td>\n

\n

Angola<\/b><\/p>\n<\/td>\n

\n

Argentina<\/b><\/p>\n<\/td>\n

\n

Australia<\/b><\/p>\n<\/td>\n

\n

…<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

\u00a0<\/b><\/p>\n<\/td>\n

\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n<\/tr>\n
\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n<\/tr>\n
\n

1997 Average<\/b><\/p>\n<\/td>\n

\n

1,277<\/p>\n<\/td>\n

\n

714<\/p>\n<\/td>\n

\n

834<\/p>\n<\/td>\n

\n

588<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

1998 Average<\/b><\/p>\n<\/td>\n

\n

1,246<\/p>\n<\/td>\n

\n

735<\/p>\n<\/td>\n

\n

847<\/p>\n<\/td>\n

\n

544<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

1999 Average<\/b><\/p>\n<\/td>\n

\n

1,202<\/p>\n<\/td>\n

\n

745<\/p>\n<\/td>\n

\n

802<\/p>\n<\/td>\n

\n

539<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

2000 Average<\/b><\/p>\n<\/td>\n

\n

1,254<\/p>\n<\/td>\n

\n

746<\/p>\n<\/td>\n

\n

761<\/p>\n<\/td>\n

\n

722<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n<\/tr>\n
\n

2001 January<\/b><\/p>\n<\/td>\n

\n

1,337<\/p>\n<\/td>\n

\n

746<\/p>\n<\/td>\n

\n

791<\/p>\n<\/td>\n

\n

708<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 February<\/b><\/p>\n<\/td>\n

\n

1,305<\/p>\n<\/td>\n

\n

749<\/p>\n<\/td>\n

\n

792<\/p>\n<\/td>\n

\n

687<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 March<\/b><\/p>\n<\/td>\n

\n

1,305<\/p>\n<\/td>\n

\n

731<\/p>\n<\/td>\n

\n

788<\/p>\n<\/td>\n

\n

699<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 April<\/b><\/p>\n<\/td>\n

\n

1,289<\/p>\n<\/td>\n

\n

739<\/p>\n<\/td>\n

\n

799<\/p>\n<\/td>\n

\n

659<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 May<\/b><\/p>\n<\/td>\n

\n

1,305<\/p>\n<\/td>\n

\n

733<\/p>\n<\/td>\n

\n

809<\/p>\n<\/td>\n

\n

627<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 June<\/b><\/p>\n<\/td>\n

\n

1,326<\/p>\n<\/td>\n

\n

728<\/p>\n<\/td>\n

\n

802<\/p>\n<\/td>\n

\n

669<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

…<\/b><\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n

\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Now, this is all right for a report, but it ain’t a normalized table that we can work with. It is in a spreadsheet, and that seems a long way from our remote SQL server. First, \u00a0we paste the four reports together to make one table. Then, we give it a little bit of a haircut in Excel so that we just get the raw data. We fix the dates so that we can export them to a database. (we simply\u00a0 put the start date in the top cell and just\u00a0 copy->fill-> series<\/b><\/i> and choose a date month increment down the column)<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

Month<\/b><\/p>\n<\/td>\n

\n

Algeria<\/b><\/p>\n<\/td>\n

\n

Angola<\/b><\/p>\n<\/td>\n

\n

Argentina<\/b><\/p>\n<\/td>\n

\n

Australia<\/b><\/p>\n<\/td>\n

\n

\u00a0<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

01\/01\/2001<\/b><\/p>\n<\/td>\n

\n

1,337<\/p>\n<\/td>\n

\n

746<\/p>\n<\/td>\n

\n

791<\/p>\n<\/td>\n

\n

708<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

01\/02\/2001<\/b><\/p>\n<\/td>\n

\n

1,305<\/p>\n<\/td>\n

\n

749<\/p>\n<\/td>\n

\n

792<\/p>\n<\/td>\n

\n

687<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

01\/03\/2001<\/b><\/p>\n<\/td>\n

\n

1,305<\/p>\n<\/td>\n

\n

731<\/p>\n<\/td>\n

\n

788<\/p>\n<\/td>\n

\n

699<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

01\/04\/2001<\/b><\/p>\n<\/td>\n

\n

1,289<\/p>\n<\/td>\n

\n

739<\/p>\n<\/td>\n

\n

799<\/p>\n<\/td>\n

\n

659<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

01\/05\/2001<\/b><\/p>\n<\/td>\n

\n

1,305<\/p>\n<\/td>\n

\n

733<\/p>\n<\/td>\n

\n

809<\/p>\n<\/td>\n

\n

627<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

01\/06\/2001<\/b><\/p>\n<\/td>\n

\n

1,326<\/p>\n<\/td>\n

\n

728<\/p>\n<\/td>\n

\n

802<\/p>\n<\/td>\n

\n

669<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

01\/07\/2001<\/b><\/p>\n<\/td>\n

\n

1,337<\/p>\n<\/td>\n

\n

713<\/p>\n<\/td>\n

\n

800<\/p>\n<\/td>\n

\n

674<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

01\/08\/2001<\/b><\/p>\n<\/td>\n

\n

1,337<\/p>\n<\/td>\n

\n

701<\/p>\n<\/td>\n

\n

808<\/p>\n<\/td>\n

\n

659<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

01\/09\/2001<\/b><\/p>\n<\/td>\n

\n

1,305<\/p>\n<\/td>\n

\n

710<\/p>\n<\/td>\n

\n

827<\/p>\n<\/td>\n

\n

635<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

01\/10\/2001<\/b><\/p>\n<\/td>\n

\n

1,284<\/p>\n<\/td>\n

\n

754<\/p>\n<\/td>\n

\n

798<\/p>\n<\/td>\n

\n

636<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

01\/11\/2001<\/b><\/p>\n<\/td>\n

\n

1,295<\/p>\n<\/td>\n

\n

785<\/p>\n<\/td>\n

\n

812<\/p>\n<\/td>\n

\n

606<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

01\/12\/2001<\/b><\/p>\n<\/td>\n

\n

1,295<\/p>\n<\/td>\n

\n

820<\/p>\n<\/td>\n

\n

801<\/p>\n<\/td>\n

\n

626<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n

\n

…<\/b><\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n

\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Looking good. Now we paste it into an Access database (the real spreadsheet is\u00a0 49 columns wide and 104 rows in height)<\/p>\n

That’s now the hard bit done. Now we simply export it from Access to an ODBC source. We use the native client driver to export it to our SQL Server. Whoosh!<\/p>\n

We now have a rather strange-looking table with data in it, that would make any database theorist hiss through their teeth. It is like this…<\/p>\n

CREATE TABLE [dbo].[OilProduction](\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ID] [int] IDENTITY(1,1) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Month] [datetime] NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Algeria] [float] NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Angola] [float] NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Argentina] [float] NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Australia] [float] NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Azerbaijan] [float] NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Brazil] [float] NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Canada] [float] NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [China] [float] NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Colombia] [float] NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Denmark] [float] NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Ecuador] [float] NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ...\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ...\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Other] [float] NULL\r\n) ON [PRIMARY]\r\n<\/pre>\n
We want a simple table so we can do the reports and analyses we choose. What you’d like is a SELECT statement consisting of a series of UNIONs.<\/p>\n
Select Month,round([Algeria],0) as TBPD, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cast('Algeria' as Varchar(80)) as country \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from OilProduction where month is not null\r\nUNION ALL\r\nSelect Month,round([Angola],0) as TBPD, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cast('Angola' as Varchar(80)) as country \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from OilProduction where month is not null\r\nUNION ALL\r\nSelect Month,round([Arab Emirates],0) as TBPD, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cast('Arab Emirates' as Varchar(80)) as country \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from OilProduction where month is not null\r\n...\r\n<\/pre>\n
But there are thirty-nine of them, so you decide to let INFORMATION_SCHEMA do the chore for you<\/p>\n
--The 'normalised' table for placing the raw data into\r\nCREATE TABLE CrudeOilProduction\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 CrudeOilProduction_ID INT IDENTITY(1, 1) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0 Date DATETIME NOT NULL,--the month of the oil production\r\n\u00a0\u00a0\u00a0\u00a0 Country VARCHAR(80) NOT NULL, --the country producing the oil\r\n\u00a0\u00a0\u00a0\u00a0 TBPD INT NOT NULL,--Thousand Barrels Per Day\r\n\u00a0\u00a0\u00a0\u00a0 insertionDate DATETIME NOT NULL\r\n\u00a0\u00a0\u00a0 )\r\nGO\r\nALTER\r\nTABLE [dbo].[CrudeOilProduction] ADD DEFAULT (getdate())FOR [insertionDate]\r\n\u00a0\r\n--we create a command string to UNPIVOT our table\r\nDECLARE @command_String VARCHAR(MAX)\r\n\u00a0\r\nSELECT\u00a0 @command_String=COALESCE(@command_String+'\r\nUNION ALL\r\n', '')+'Select Month,round(['+column_Name+'],0) as TBPD, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cast('''+column_Name+''' as Varchar(80)) as country \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from OilProduction where month is not null'\r\nFROM\u00a0\u00a0\u00a0 information_Schema.columns\r\nWHERE\u00a0\u00a0 table_Name LIKE 'OilProduction'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND column_Name NOT IN ('id', 'month')\r\nORDER BY column_Name\r\n--and insert the data from the pivot table\r\nSELECT\u00a0 @command_String\r\nINSERT\u00a0 INTO CrudeOilProduction (Date, TBPD, Country)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXECUTE (@command_String)\r\n<\/pre>\n
\u00a0This will give you a table<\/p>\n
1\u00a0\u00a0\u00a02001-01-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Algeria\u00a0 1337\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n2\u00a0\u00a0\u00a02001-02-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Algeria\u00a0 1305\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n3\u00a0\u00a0\u00a02001-03-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Algeria\u00a0 1305\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n4\u00a0\u00a0\u00a02001-04-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Algeria\u00a0 1289\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n5\u00a0\u00a0\u00a02001-05-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Algeria\u00a0 1305\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n6\u00a0\u00a0\u00a02001-06-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Algeria\u00a0 1326\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n7\u00a0\u00a0\u00a02001-07-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Algeria\u00a0 1337\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n8\u00a0\u00a0\u00a02001-08-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Algeria\u00a0 1337\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n9\u00a0\u00a0\u00a02001-09-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Algeria\u00a0 1305\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n10\u00a0\u00a02001-10-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0Algeria\u00a0 1284\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n11\u00a0\u00a02001-11-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0Algeria\u00a0 1295\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n12\u00a0\u00a02001-12-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Algeria\u00a0 1295\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n13\u00a0\u00a02002-01-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Algeria\u00a0 1221\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n14\u00a0\u00a02002-02-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0Algeria\u00a0 1215\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n15\u00a0\u00a02002-03-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Algeria\u00a0 1235\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-11-10 14:33:00.637\r\n...etc...\r\n<\/pre>\n
Each row represents the monthly figure for the daily production of oil. Why insertion date? You’ll need this once you’ve done your aggregation and you insert or change some data retrospectively.\u00a0 This won’t matter in a table with only\u00a0 3914 rows, but if you have several million rows, it will be a time-saver. \u00a0You may not thank me for requiring you to use such a large table on a small server, though. We need a second table which I’ve prepared as an excel file and a build script, to enable us to do some more interesting\u00a0 breakdowns of the data<\/p>\n
CREATE TABLE [dbo].[Regions](\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Country_ID] [int] IDENTITY(1,1) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [country] [varchar](30) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [continent] [varchar](80) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [region] [varchar](80) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [opec_member] [int] NOT NULL \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT [DF_Regions_opec_member]\u00a0 DEFAULT ((0))\r\n) ON [PRIMARY]\r\n<\/pre>\n
You can try the exercise of getting the data in via Access, or you can just use the build script I’ve provided.<\/p>\n
Now, let’s just try out a few simple manipulations.<\/p>\n
--Now, lets try out the CUBE\r\nSELECT \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 country, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [year]=DATEPART(year,Date),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Crude Oil Production] =SUM(TBPD*Datediff(Day,Date,DateAdd(month,1,Date))) \r\n\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 --to calculate the production in thousands of barrels in the month \r\n\r\n\r\nFROM\u00a0\u00a0\u00a0\u00a0 CrudeOilProduction \r\nGROUP BY country,DATEPART(year,Date)\r\nWITH CUBE\r\n<\/pre>\n
\"862-Wierd1.jpg\"The result\u00a0 looks weird. It breaks all the rules, because it has nulls, and the nulls don’t mean ‘Unknown’ either, they mean ‘total’. This takes some getting used-to. This sort of result wasn’t designed for human consumption.<\/p>\n
Basically, you can keep tabs on what the total means by seeing which columns are NULL. In the row below the total for the Yemen (39285) you’ll see a null for the country column and the year column, meaning that this is the total for all years and all countries. If you just mentally substitute the word ‘All’ for NULL then you can’t go far wrong. If all this seems bizarre to you, then you can ignore this and use GROUPING() instead to tell you whether the row is a total. In my aggregate table below, I do both just so you can try different approaches. May Codd forgive us for this!<\/p>\n
We’ll put the results in a temporary table. In practice, the original data will be much larger, so the saving in time will be much greater. This sort of table is normally called an ‘Aggregate’ table or ‘Aggregation’ table.<\/p>\n
if exists (select 1 from tempdb.information_schema.tables\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 where table_name like '#OilProducersAggregate%')DROP TABLE #OilProducersAggregate\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nCREATE TABLE #OilProducersAggregate\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 country VARCHAR(25),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [year] CHAR(4),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 production INT,--Crude oil production in Thousand Barrels\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 AvgProduction INT,--Average Monthly production (Thousand Barrels) \r\n\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 MaxInsertionDate DateTime,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [CountryGrouping] INT, --is this an aggregation row\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [YearGrouping] INT, --is this an aggregation row\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\r\nINSERT INTO #OilProducersAggregate\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (Country, [Year], production, AvgProduction, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MaxInsertionDate,CountryGrouping,YearGrouping)\r\nSELECT\u00a0 [country],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [year]=DATENAME(year,Date),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [production]=SUM(TBPD*Datediff(Day,Date,DateAdd(month,1,Date))), \r\n\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [AvgProduction]=AVG(TBPD*Datediff(Day,Date,DateAdd(month,1,Date))), \r\n\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [MaxInsertionDate]=max(insertionDate),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [CountryGrouping]=GROUPING(country),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [YearGrouping]=GROUPING(DATENAME(year,Date))\r\nFROM CrudeOilProduction\r\nGROUP BY country,DATENAME(year,Date) WITH CUBE\u00a0 \r\n<\/pre>\n
Something that is more understandable comes from using the results of a CUBE with a PIVOT.<\/p>\n
SELECT country, [2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008], [2009] AS [2009 (part)], [Total]\r\nFROM\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0\u00a0\u00a0\u00a0\u00a0 [country]=COALESCE(country,'All'),--to get a row labelled 'All'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [year]=COALESCE([year],'Total'),--to get a column headed 'Total'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [production],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Countrygrouping]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM #OilProducersAggregate--just 351 rows. No sweat\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ) s \r\nPIVOT (SUM(production) FOR [Year]\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 IN ([2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008],[2009], [Total])) AS p\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0 ORDER BY [Countrygrouping],country\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n<\/pre>\n
\"862-Country2.jpg\"<\/p>\n
Notice that I”m using the GROUPING() information stored in the aggregate table to sort the order of the rows so that the total is where it should be on the bottom line.<\/p>\n
With a minor modification, we will give you the average monthly production. Notice we can still use the ‘SUM’ function for the pivot, since the is only one row for each cell.<\/p>\n
SELECT country, [2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008],[2009] \u00a0AS [2009 (part)], [Total]\r\nFROM\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0\u00a0\u00a0\u00a0\u00a0 [country]=COALESCE(country,'All'),--to get a row labelled 'All'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [year]=COALESCE([year],'Total'),--to get a column headed 'Total'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [production]=avgProduction,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Countrygrouping]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM #OilProducersAggregate--just 351 rows. No sweat\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ) s \r\nPIVOT (SUM(production) FOR [Year]\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 IN ([2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008],[2009], [Total])) AS p\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY [Countrygrouping],country\u00a0 \r\n<\/pre>\n
If you don’t have the pivot operator, (SQL Server 2005 or above) it doesn’t really matter. The old ways were almost as good though more verbose.<\/p>\n
\u00a0\r\nSELECT \u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0country,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0[2001]=SUM(CASE WHEN year ='2001' THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0[2002]=SUM(CASE WHEN year ='2002' THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0[2003]=SUM(CASE WHEN year ='2003' THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0[2004]=SUM(CASE WHEN year ='2004' THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0[2005]=SUM(CASE WHEN year ='2005' THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0[2006]=SUM(CASE WHEN year ='2006' THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0[2007]=SUM(CASE WHEN year ='2007' THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0[2008]=SUM(CASE WHEN year ='2008' THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0[2009 (part)]=SUM(CASE WHEN year ='2009' THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0[Total]=SUM(CASE WHEN year ='Total' THEN production ELSE 0 END)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0\u00a0\u00a0\u00a0\u00a0 [country]=COALESCE(country,'All'),--to get a row labelled 'All'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [year]=COALESCE([year],'Total'),--to get a column headed 'Total'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [production]=avgProduction,--just change this line to get the totel production.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Countrygrouping]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM #OilProducersAggregate--just 351 rows. No sweat\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ) s \r\nGROUP BY country\r\nORDER BY MAX([Countrygrouping]),country\r\n\u00a0\r\n<\/pre>\n
“very nice but what we actually wanted was a breakdown by continent”<\/i><\/p>\n
–“Right away, Sir!”<\/i><\/p>\n
We have a slight problem because the original data had an ‘other’ field for countries. We get over this by doing a left outer join with our list of regions, and pick up the unmatched countries) as ‘unmatched’ neatly sidestepping the\u00a0 fact that total lines will also have a null in the country column by checking the ‘grouping’ column*\/<\/p>\n
SELECT \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 continent, [2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008], [2009] AS [2009 (part)], [Total]\r\nFROM\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0\u00a0\u00a0\u00a0\u00a0 [continent]=CASE WHEN GROUPING(continent)=0 AND continent IS NULL \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN 'unclassified'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN GROUPING(continent)=1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN 'All' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE continent END,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [year]=COALESCE([year],'Total'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [production]=SUM(production),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [grouping]=GROUPING(continent)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM #OilProducersAggregate s\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 LEFT OUTER JOIN regions o\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ON o.country = s.country\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE s.YearGrouping=0 AND countryGrouping=0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 GROUP BY continent,year WITH CUBE) g\r\nPIVOT (SUM(production) FOR [Year] \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 IN ([2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008], [2009], [Total])) AS p\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY [grouping],total DESC\r\n<\/pre>\n
\u00a0\"862-Continent3.jpg\"<\/p>\n
–‘What about the breakdown by Opec\/Non opec countries? And rotate it the other way while you’re about it’<\/i><\/p>\n
‘No problem, boss’<\/i><\/p>\n
\u00a0\r\nSELECT\u00a0\u00a0\u00a0\u00a0\u00a0 [year]=COALESCE([year],'Total'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [OPEC]=SUM(CASE WHEN opec_member=1 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Non-OPEC]=SUM(CASE WHEN opec_member=0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Both]=SUM(production)\r\nFROM #OilProducersAggregate s\r\nLEFT OUTER JOIN regions o\r\nON o.country = s.country\r\nWHERE s.YearGrouping=0 AND countryGrouping=0\r\nGROUP BY year WITH CUBE\r\nORDER BY GROUPING(year),CONVERT(INT,year)\r\n\u00a0\r\n<\/pre>\n
\"862-Opec.jpg\"
\nWe could have used Rollup, instead of cube, for this last report …<\/p>\n
…and a simple modification will get you the yearly breakdown, OPEC vs non OPEC<\/p>\n
\u00a0SELECT\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [year]=COALESCE([year],'Total'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Africa OPEC]=SUM(CASE WHEN opec_member=1 AND continent ='Africa' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Africa Non-OPEC]=SUM(CASE WHEN opec_member=0 AND continent ='Africa'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Asia OPEC]=SUM(CASE WHEN opec_member=1\u00a0 AND continent ='Asia'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Asia Non-OPEC]=SUM(CASE WHEN opec_member=0 AND continent ='Asia' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Europe OPEC]=SUM(CASE WHEN opec_member=1 AND continent ='Europe'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Europe Non-OPEC]=SUM(CASE WHEN opec_member=0 AND continent ='Europe' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [North America OPEC]=SUM(CASE WHEN opec_member=1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND continent ='North America' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [North America Non-OPEC]=SUM(CASE WHEN opec_member=0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND continent ='North America' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [South America OPEC]=SUM(CASE WHEN opec_member=1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND continent ='Latin America and the Caribbean' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [South America Non-OPEC]=SUM(CASE WHEN opec_member=0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND continent ='Latin America and the Caribbean' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Oceania OPEC]=SUM(CASE WHEN opec_member=1 AND continent ='Oceania' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Oceania Non-OPEC]=SUM(CASE WHEN opec_member=0 AND continent ='Oceania' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Russia OPEC]=SUM(CASE WHEN opec_member=1 AND continent ='Russia' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Russia Non-OPEC]=SUM(CASE WHEN opec_member=0 AND continent ='Russia' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [OPEC]=SUM(CASE WHEN opec_member=1\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Non-OPEC]=SUM(CASE WHEN opec_member=0\u00a0 THEN production ELSE 0 END),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [all]=SUM(production)\r\nFROM #OilProducersAggregate s\r\nLEFT OUTER JOIN regions o\r\nON o.country = s.country\r\nWHERE s.YearGrouping=0 AND countryGrouping=0\r\nGROUP BY year WITH ROLLUP\r\nORDER BY GROUPING(year),CONVERT(INT,year)\r\n<\/pre>\n
\"862-OpecContinent.jpg\"<\/p>\n
SELECT\r\n\u00a0COALESCE(region,'other') AS region,\r\n\u00a0[2001]=SUM(CASE WHEN year ='2001' THEN production ELSE 0 END),\r\n\u00a0[2002]=SUM(CASE WHEN year ='2002' THEN production ELSE 0 END),\r\n\u00a0[2003]=SUM(CASE WHEN year ='2003' THEN production ELSE 0 END),\r\n\u00a0[2004]=SUM(CASE WHEN year ='2004' THEN production ELSE 0 END),\r\n\u00a0[2005]=SUM(CASE WHEN year ='2005' THEN production ELSE 0 END),\r\n\u00a0[2006]=SUM(CASE WHEN year ='2006' THEN production ELSE 0 END),\r\n\u00a0[2007]=SUM(CASE WHEN year ='2007' THEN production ELSE 0 END),\r\n\u00a0[2008]=SUM(CASE WHEN year ='2008' THEN production ELSE 0 END),\r\n\u00a0[Total]=SUM(production)\r\n\u00a0FROM #OilProducersAggregate s\r\nLEFT OUTER JOIN regions o\r\nON o.country = s.country\r\nWHERE s.YearGrouping=0 AND countryGrouping=0\r\nGROUP BY region\r\nUNION ALL\r\nSELECT\r\n\u00a0'Sum',\r\n\u00a0[2001]=SUM(CASE WHEN year ='2001' THEN production ELSE 0 END),\r\n\u00a0[2002]=SUM(CASE WHEN year ='2002' THEN production ELSE 0 END),\r\n\u00a0[2003]=SUM(CASE WHEN year ='2003' THEN production ELSE 0 END),\r\n\u00a0[2004]=SUM(CASE WHEN year ='2004' THEN production ELSE 0 END),\r\n\u00a0[2005]=SUM(CASE WHEN year ='2005' THEN production ELSE 0 END),\r\n\u00a0[2006]=SUM(CASE WHEN year ='2006' THEN production ELSE 0 END),\r\n\u00a0[2007]=SUM(CASE WHEN year ='2007' THEN production ELSE 0 END),\r\n\u00a0[2008]=SUM(CASE WHEN year ='2008' THEN production ELSE 0 END),\r\n\u00a0total=SUM(production)\r\nFROM #OilProducersAggregate s\r\nWHEREs.YearGrouping=0 ANDcountryGrouping=0\r\n<\/pre>\n
\n\u00a0\"862-Region6.jpg\"<\/p>\n
So why did we put an insertion date into the raw and aggregate data? This was so we can re-aggregate all or part of the data when something gets changed. I must admit that, for an accounting system, i’d want to know why, rather than just redo the aggregation, but in this example we’d just re-aggregate if this SQL produces a non-zero result.<\/p>\n
SELECT DISTINCT s.year, s.country \r\nFROM CrudeOilProduction c\r\n\u00a0\u00a0 INNER JOIN #OilProducersAggregate s\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ON\u00a0 s.country =c.country\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 AND s.[year]=DATENAME(year,c.Date)\r\nWHERE insertionDate>MaxInsertionDate\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\r\n<\/pre>\n
Where you have many millions of rows, and only want to alter any totals that have changed, the logic can get quite tricky as you have to redo all the dependent totals without recalculating everything, and the logic for a cube can get very messy indeed, way too\u00a0 much to describe in this article.<\/p>\n
Conclusions<\/h1>\n
Well, once you have a cube in place, there is a great deal of reporting you can do with it without having to go back to the raw data. Although the CUBE operator is now made to look pretty archaic when compared with what SSAS offers, there is still a lot of life left in the technique for doing ad-hoc reports on-demand without the overhead of SSAS. For supplying data to Excel or reporting services, it can be a godsend.<\/p>\n
Pivoting and un-pivoting is simple to do. In another article, The Cross-Tab Pivot Table Workbench<\/a>, Robyn and I have shown how to do a variety of such things, and even how to go into mass production with pivot tables without bothering with reporting services,<\/p>\n
The other conclusion from the data is that, at the time of writing, we have a lot of work to do in reducing our dependence on fossil fuel. The figures for oil production show that the downturn hasn’t made a huge difference to demand!\u00a0 Never mind. In Simple-Talk’s offices, they’ve changed the light-bulb for a fluorescent, in a bid to save the planet.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
In which Phil is asked to write a nice simple quick-start guide about aggregation, pivoting and un-pivoting techniques.  To do so, he takes us all the way from getting the data from a published source, transferring it to SQL Server, un-pivoting it, storing it in a relational table, aggregating it and finally pivoting the data in a variety of ways…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4179,4150,4151,4252],"coauthors":[6813],"class_list":["post-727","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-source-control","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/727","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=727"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/727\/revisions"}],"predecessor-version":[{"id":73017,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/727\/revisions\/73017"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=727"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=727"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=727"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=727"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}