DYNAMIC SQL PIVOT INTO TEMP TABLE

Today I was writing a stored procedure and had the need to create a dynamic PIVOT query in T-SQL and put the results in a temporary table. Not being a regular PIVOT user I figured this was a well known and solved issue.

Surprisingly, my first google hits provided no relief. Some posts I found mentioned OPENQUERY or OPENROWSET or using global temp tables. There were posts about dynamic pivots, but the temp table issue was unresolved. sp_executesql opens another session, temp tables created within it fall out of scope, you can’t create the temp table in advance because you don’t know all the columns, etc.

I thought about it for a while and realized the solution, just create the temp table with one column, and then dynamically alter it. A working example is below.

This example will be all temp tables so its easy to run, but a true real world example would have the sales and manufacturer info in persistent tables, primary keys, foreign keys, indexes etc. etc.

First lets create two tables to report on. Most PIVOT examples use some arbitrary piece of data like a name or date text as the column names. Not a fan of this myself. The source of my data will be three simple tables, #Manufacturers, #Years and #Sales

Note: Apparently the T-SQL syntax highlighter I use doesn’t like NVARCHAR(MAX), sp_executesql, or QUOTENAME(), hence the lame coloring for now.

First Manufacturers:

CREATE TABLE #Manufacturers
(
	ManufacturerID INT PRIMARY KEY,
	Name VARCHAR(128)
)
 
INSERT INTO #Manufacturers (ManufacturerID, Name)
VALUES (1,'Dell')
INSERT INTO #Manufacturers (ManufacturerID, Name)
VALUES (2,'Lenovo')
INSERT INTO #Manufacturers (ManufacturerID, Name)
VALUES (3,'HP')

Next #Years. Now Year is actually one of those names that actually works ok as a column name if you are doing a single pivot. 2014 or 2015 as the column name is self explanatory and easy to deal with programatically. However, I will use the #Years table to show how to rename the column header dynamically.

CREATE TABLE #Years
(YearID INT, Description VARCHAR(128))
GO

INSERT INTO #Years (YearID, Description) VALUES (1, '2014')
INSERT INTO #Years (YearID, Description) VALUES (2, '2015')
GO

Finally #Sales. Note the revenue figures, these are semi-made up but I believe to be in the ballpark range. Yes it is a lot of money! Here is our #Sales table

CREATE TABLE #Sales
(ManufacturerID INT, YearID INT,Revenue MONEY)
GO

INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(1,2,59000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(2,2,46000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(3,2,111500000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(1,1,55000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(2,1,42000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(3,1,101500000000)
GO

Next we need to build some strings for the columns to be used in our dynamic SQL statements

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @PivotColumnName AS NVARCHAR(MAX)
DECLARE @TempTableColumnName AS NVARCHAR(MAX)
DECLARE @AlterTempTable AS NVARCHAR(MAX)
 
--get delimited column names for various SQL statements below
SELECT 
	-- column names for pivot
	@PivotColumnName= ISNULL(@PivotColumnName + N',',N'') + QUOTENAME(CONVERT(NVARCHAR(10),YearID)),
	-- column names for insert into temp table
	@TempTableColumnName = ISNULL(@TempTableColumnName + N',',N'') + QUOTENAME('Y' + CONVERT(NVARCHAR(10),YearID)),
	-- column names for alteration of temp table
	@AlterTempTable = ISNULL(@AlterTempTable + N',',N'') + QUOTENAME('Y' + CONVERT(NVARCHAR(10),YearID)) + ' MONEY' 
FROM (SELECT DISTINCT [YearID] FROM #Sales) AS Sales

I probably could use a single string rather than three, and do some string replacements in every usage, but I figured the above is the clearest. In this case I prefix the YearID with a ‘Y’.

Next, create our Pivot temp table

CREATE TABLE #Pivot
(
	 ManufacturerID INT
)

-- Thats it! Because the following step will flesh it out.

SET @SQL = 'ALTER TABLE #Pivot ADD ' + @AlterTempTable
EXEC sp_executesql @SQL

Note: If for some reason you did not know any field names in advance, you could create the temp table with a IDENTITY column that you just ignore, like

/* 
CREATE TABLE #Pivot
(
     IgnoreMe INT IDENTITY(1,1)
)
*/

Now we can just execute the dynamic PIVOT

--execute the dynamic PIVOT query into the temp table 
SET @SQL =  N'
	INSERT INTO #Pivot (ManufacturerID, ' + @TempTableColumnName + ')
	SELECT ManufacturerID, ' + @PivotColumnName + '
	FROM #Sales S
	PIVOT(SUM(Revenue) 
	  FOR S.YearID IN (' + @PivotColumnName + ')) AS PivotTable'
EXEC sp_executesql @SQL

Now we can return our results like so

SELECT M.Name, P.*
FROM #Manufacturers M
INNER JOIN #Pivot P ON M.ManufacturerID = P.ManufacturerID

This results in

Finally, placing the a leading character in the column name may seem like an unnecessary step, but if you had two dynamic pivots in your stored procedure, say one has CategoryID as the column header and another had RegionID, or another key field in the database as the header, how to tell them apart if you returned as a single joined result? A clash is a possibility if CategoryID=1 exists and you also have RegionID =1. Using a C and R prefix in that case will prevent clashes when joining the temp tables.

ASP.NET Core 1.0

Since my last post the name has been changed to ASP.NET Core 1.0.

Starting over to use the latest & greatest I uninstalled the previous packages in Ubuntu

I then Installed the Debian package from

https://github.com/dotnet/cli

I downloaded the latest samples from David Fowler

https://github.com/aspnet/cli-samples

Currently the HelloWeb code isn’t restoring for me (Unable to restore Microsoft.AspNetCore.Server.Kestrel), I assume this is because of as this moment the Ubuntu code isn’t building according to the CLI page above.

Instead, if I use my previous simple console app from here, and change the project.json to

{
    "version": "1.0.0-*",
    "compilationOptions": {
        "emitEntryPoint": true
    },
 
    "dependencies": {
        "NETStandard.Library": "1.0.0-*"
    },
 
    "frameworks": {
        "dnxcore50": { }
    }
}

And then change to the project directory and restore using the latest bits

dotnet restore -s https://myget.org/f/dotnet-core

It will build and run.

Ubuntu : Recovering from /etc/fstab mistake

I wasted a bit of time on this today, so I decided to post the situation and a fix if it comes up for others.

I am running Ubuntu on a VM in HyperV. I edited the mount entry for a Windows share in /etc/fstab but made a mistake on the password.

Afterward, Ubuntu would not boot all the way though, it would get stuck on the boot screen with the message “Press S to skip mounting or M for manual recovery”. Well there is some issue here because at least in the hyperv machine connection, the M and S keys had no effect [note: key order intentionally reversed in last sentence to avoid unintended SEO 😉 ].

looking for solutions, I came up with some that recommended using fdisk etc. but I think those solutions are dated. The solution for me was:

1) In HyperV settings, Load Ubuntu Desktop ISO into DVD drive, in firmware set to boot first.
2) When the ISO image loads, it will offer an option to try Ubuntu without installing, select this.
3) Once the desktop is loaded, run terminal by pressing Ctrl-Alt-T

4) then run:

sudo gparted

if gparted isn’t installed, run the command

sudo apt-get install gparted

5) Inspect the drives listed in the GUI, just from what I knew about the drive sizes, I was able to determine the OS drive was ‘/dev/sda3’ and the file system type was listed as ‘ext4’

I then mounted this

sudo mount -t ext4 /dev/sda3 /mnt

Afterward I edited the /etc/fstab file, fixed the issue and then rebooted

sudo gedit /mnt/etc/fstab

Also, I added the “nobootwait” and “nofail” options to the fstab entry, and will add to future similar entries, so as not to have this issue again


References

nofail and nobootwait mount options in fstab prevent boot problems : http://techmonks.net/nofail-and-nobootwait-mount-options-in-fstab-prevent-boot-problems/

Ubuntu : Increasing screen resolution in VM

Quick tip for boosting resolution in a Ubuntu VM in HyperV

from terminal

sudo gedit /etc/default/grub

Edit or add the the assignment to RUB_CMDLINE_LINUX_DEFAULT

RUB_CMDLINE_LINUX_DEFAULT="quiet splash video=hyperv_fb:1920x1080"

I chose 1920×1080 as this is the current maximum that the Hyper-V machine connection will support and it is less than what my monitor supports, change this to a value that makes sense for you.

After saving run:

sudo update-grub

And then reboot your virtual machine

ASP.NET 5 Pre-RC 2 On Linux – Part III

The next step is to try and run a web app.

There are samples under David Fowler’s (ASP.NET team member) github repository.

https://github.com/davidfowl/dotnetcli-aspnet5

I downloaded the .zip file, copied it to the Code folder from my previous post and extracted it.

I then switched to the HelloWeb sample folder in terminal

cd ~/Documents/Code/dotnetcli-aspnet5-master/HelloWeb

dotnet restore

(downloads 121 packages)

dotnet run

We get the following error

Screen Shot 2016-01-06 at 12.13.43 AM

So libuv needs to be installed as it is not present

install libuv per instructions

sudo apt-get install make automake libtool curl
curl -sSL https://github.com/libuv/libuv/archive/v1.4.2.tar.gz | sudo tar zxfv - -C /usr/local/src
cd /usr/local/src/libuv-1.4.2
sudo sh autogen.sh
sudo ./configure
sudo make
sudo make install
sudo rm -rf /usr/local/src/libuv-1.4.2 && cd ~/
sudo ldconfig

Now we try again and success!

Screen Shot 2016-01-06 at 12.21.54 AM


Note that there looks like is a newer streamlined process for installing on Ubuntu here, but that doesn’t work for me, I reported it.