May 17, 2008 at 11:40 am
I don't know how many people have seen this, but in case no one has, I thought I'd post it.
I accidently made a GREAT discovery using the UNPIVOT operator in T-SQL.
I have a table with 8 smallmoney columns and an ID column.
CREATE TABLE [dbo].[Staging_DimCovBuckets](
[ProductID] [int] NULL,
[CarrSplit] [smallmoney] NULL,
[HouSplit] [smallmoney] NULL,
[RefOverSplit] [smallmoney] NULL,
[EscrowSplit] [smallmoney] NULL,
[CancelFee] [smallmoney] NULL,
[TermHou] [smallmoney] NULL,
[TermEscrow] [smallmoney] NULL,
[TermDealer] [smallmoney] NULL)
ON PRIMARY
Per my boss, he wants this data put into a table where each individual bucket is on a
separate record. We have a DimCoverageBucket lookup table (see below code) which
contains all the above column names as individual items and then it goes into a "join" table
as a key.
CREATE TABLE [dbo].[DimCoverageBucket](
[CovBucketKey] [int] IDENTITY(1,1) NOT NULL,
[BucketName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TimeKey] [int] NULL,
PRIMARY KEY CLUSTERED
([CovBucketKey] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I'm not going to code the insert of the column names. Just assume that everything except
the ID column is going to be in the DimCoverageBucket table. Below is the JOIN table.
CREATE TABLE [dbo].[FactCoverageBucketDetail](
[CovBucketDetailKey] [int] IDENTITY(1,1) NOT NULL,
[CovBucketKey] [int] NOT NULL,
[CovBucketAmount] [smallmoney] NULL,
[EntityTypeNameKey] [int] NOT NULL,
[TimeKey] [int] NULL,
[EntityTypeKey] [int] NULL
PRIMARY KEY CLUSTERED
([CovBucketDetailKey] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
You can see where things get complicated, I'm sure, at this point. I have to parse
8 buckets into 8 individual records. And then I have to make sure to get the
CovBucketKey correct for each entry. GAH!
So I'm playing around with UNPIVOT and come up with the following code to split out the
ID and individual buckets.
Select ProductID, SplitValue
from (Select ProductID, CarrSplit, HouSplit, RefOverSplit,
EscrowSplit, CancelFee, TermHou, TermEscrow,TermDealer
from dbo.Staging_DimCovBuckets) scb
UNPIVOT (SplitValue FOR ProductID IN (CarrSplit, HouSplit,
RefOverSplit, EscrowSplit, CancelFee, TermHou,
TermEscrow, TermDealer) ) AS MyDetails;
Only this code doesn't work. I get the following error:
Msg 265, Level 16, State 1, Line 1
The column name "ProductID" specified in the UNPIVOT operator
conflicts with the existing column name in the UNPIVOT argument.
Msg 8156, Level 16, State 1, Line 1
The column 'ProductID' was specified multiple times for 'MyDetails'.
Looking at BOL, the examples shown need 3 different columns to do an UNPIVOT properly.
This is terribly annoying.. GRRR. I only have the ID key and the buckets.
So what's a girl to do?
Answer: Add an identity key to the original Staging table. And this Identity key might
help me with my WHILE loop for loading the tables. (Some of you have already figured
out a better solution at this point, but bear with me. I'm going somewhere with this
story).
so...
Alter Table Staging_DimCovBuckets
Add DetKey int Identity(1,1) NOT NULL
Looking at the Staging table, everything looks nifty and neat. I start coding a WHILE loop,
get myself confused, then decide to pull my data into a Temp table with a second identity
key just to see what I'll see.
Select ProductID, SplitValue, Identity (int,1,1) as BucketNum
into #MyTemp
from (Select ProductID, CarrSplit, HouSplit, RefOverSplit, EscrowSplit,
CancelFee, TermHou, TermEscrow, TermDealer
from dbo.Staging_DimCovBuckets) scb
UNPIVOT (SplitValue FOR DetKey IN (CarrSplit, HouSplit,
RefOverSplit, EscrowSplit, CancelFee, TermHou,
TermEscrow, TermDealer) ) AS MyDetails;
Select * from #MyTemp;
Drop Table #MyTemp;
As you can see, if you're following my code, the BucketNum doesn't do a whole lot to help.
I've got multiples of 8 in my new identity key. Example data is:
ProductID SplitValue BucketNum
14969060.001
14969060.002
14969060.003
14969060.004
14969060.005
14969060.006
14969060.007
14969060.008
8529067.359
8529065.2510
8529060.0011
8529063.1512
85290625.0013
8529060.0014
8529060.0015
8529060.0016
So, I decide to throw the DetKey from my original table into the Mix here. Let's see what
numbers I can mix & match....
Select ProductID, SplitValue, Identity (int,1,1) as BucketNum,
DetKey
into #MyTemp
from (Select ProductID, CarrSplit, HouSplit, RefOverSplit, EscrowSplit,
CancelFee, TermHou, TermEscrow, TermDealer
from dbo.Staging_DimCovBuckets) scb
UNPIVOT (SplitValue FOR DetKey IN (CarrSplit, HouSplit,
RefOverSplit, EscrowSplit, CancelFee, TermHou,
TermEscrow, TermDealer) ) AS MyDetails;
Select * from #MyTemp;
Drop Table #MyTemp;
And instead of another integar column, these are the results I get:
ProductID SplitValue BucketNum DetKey
14969060.001CarrSplit
14969060.002HouSplit
14969060.003RefOverSplit
14969060.004EscrowSplit
14969060.005CancelFee
14969060.006TermHou
14969060.007TermEscrow
14969060.008TermDealer
8529067.359CarrSplit
8529065.2510HouSplit
8529060.0011RefOverSplit
8529063.1512EscrowSplit
85290625.0013CancelFee
8529060.0014TermHouse
8529060.0015TermEscrow
8529060.0016TermDealer
AHA!!!! I did not know UNPIVOT would GIVE me the bucket names. SOOOOOO
COOOL!
So I drop the WHILE Loop idea and the Temp table idea and here's my final source code
(leaving the DetKey identity field on the Staging table):
Select ProductID, SplitValue, DetKey
from (Select ProductID, CarrSplit, HouSplit, RefOverSplit, EscrowSplit,
CancelFee, TermHou, TermEscrow, TermDealer
from dbo.Staging_DimCovBuckets) scb
UNPIVOT (SplitValue FOR DetKey IN (CarrSplit, HouSplit,
RefOverSplit, EscrowSplit, CancelFee, TermHou,
TermEscrow, TermDealer) ) AS MyDetails;
And what previously promised to be a very painfully, mindnumbing and hours long
process of writing loop code and debugging has suddenly turned into a few minutes
worth of work that has just made my datawarehouse loading SOOOO much easier.
WHEEEE!
I hope this helps someone out. Let me know if you have any questions. Like I said, I
completely discovered this by accident. BOL doesn't even contain a reference like this
that I found.
May 17, 2008 at 11:42 am
Oh, the reason I needed the bucket names.....
I can now do a JOIN between the Staging table and the DimCoverageBucket table on the BucketName so that I get the proper key in my third "join" table.
Just an FYI for those who didn't understand why this excites me so much. @=)
May 17, 2008 at 12:51 pm
Wonderful job, Brandie... it's great! You should have submitted this as an article! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply