Login / Register  search  syndication  about

          Jason Buss' Blog

Jason Buss on SalesLogix development & customization, SQL, and more.

Advanced Pivoting in MSSQL

Using pivot queries in SQL is a great tool for rotating data, but what if you need to do something more complicated with multiple data sources and multiple columns?  Recently, I had a request to pivot some data from multiple similar tables and to rotate that data into a columnar format.

I hadn't played much with pivot queries in MSSQL, so initially I thought I could accomplish it simply using that type of query.  However, with the number of columns to include in the pivot, and the potential for an unknown number of columns, I found that I had to create a couple of stored procedures to process the data.  Here's a simplified version of what I did:

I started out with three different tables  Test1, Test2 and Test3.  Each table contains the same number of fields:

PrimaryKey
ForeignKey
Description
Amount

The goal was to rotate and combine the values so that we would end up with a dataset containing a column for each Description and Amount row in each table.  This becomes complicated because you have no way of knowing just how many columns you would eventually end up with.

As a first step, I created a view to join the data from all three tables:

 CREATE VIEW [dbo].[vUnionTestTables]
AS
SELECT PrimaryKey, ForeignKey, 'Test1' AS FromTable, description, amount
FROM dbo.Test1
UNION
SELECT PrimaryKey, ForeignKey, 'Test2' AS FromTable, description, amount
FROM dbo.Test2
UNION
SELECT PrimaryKey, ForeignKey, 'Test3' AS FromTable, description, amount
FROM dbo.Test3
GO

After the view is created, I created a stored Procedure that does that uses temporary tables to process the current data and stuff the data into additional tables created by the procedure:

 CREATE procedure [dbo].[sp_TablesSnapshot] @DataPart varchar(30) as
DECLARE @FromShort char(3)
If @DataPart = 'Test1' set @FromShort = 'T1'
else
If @DataPart = 'Test2' set @FromShort = 'T2'
else
If @DataPart = 'Test3' set @FromShort = 'T3'
declare @PivotTableX TABLE
(
ID Int,
RECORDID VARCHAR(30),
FROMTABLE VARCHAR(50),
DESCRIPTION NCHAR(100),
AMOUNT NVARCHAR(4000),
Processed NCHAR(1)
)
declare @RowCount Int
Set @RowCount = (Select Count(ForeignKey) from dbo.vUnionTestTables)
Declare @I INT
Set @I = 1
WHILE (@I <= @RowCount)
BEGIN
DECLARE @PrimaryKey INT, @ForeignKey INT, @FromTable VARCHAR(3), @Description VARCHAR(100), @Amount NVARCHAR(4000)
SELECT @PrimaryKey = PrimaryKey
, @ForeignKey = ForeignKey
, @FromTable =
Case FromTable
WHEN 'Test1' Then 'T1'
WHEN 'Test2' Then 'T2'
WHEN 'Test3' Then 'T3'
END
, @Description = description
, @Amount = Amount

FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY ForeignKey) AS [RowNo] FROM dbo.vUnionTestTables) t1
WHERE t1.RowNo = @I
And
FromTable = @DataPart
Insert into @PivotTableX (ID, RECORDID, FROMTABLE, DESCRIPTION, AMOUNT, Processed)
values (@PrimaryKey, @ForeignKey, @FromTable, @Description, @Amount, 'F')
Set @I = @I + 1
END
declare @PivotTable TABLE
(
ID Int,
RECORDID VARCHAR(30),
FROMTABLE VARCHAR(50),
DESCRIPTION NCHAR(100),
AMOUNT NVARCHAR(4000),
Processed NCHAR(1)
)
insert into @PivotTable (ID,RECORDID,FROMTABLE,DESCRIPTION,AMOUNT,PROCESSED) select distinct * from @PivotTableX where ID is not null
Declare @MaxCount int
declare @CountTable table
(
RecordID int,
FromTable nchar(3),
idCount int
)
insert into @CountTable
(RecordID,FromTable,idCount)
select RECORDID, FromTable, COUNT(ID) from @PivotTable
GROUP by RECORDID, FromTable order by RECORDID
Set @MaxCount = (select MAX(idcount) from @CountTable where FromTable = @FromShort and RecordID is not null)
Declare @ColumnsErString varchar(5000)
Declare @ColumnsMeString varchar(5000)
Declare @ColumnsQmrString varchar(5000)
Declare @BuildColumnsString varchar(5000)
Set @BuildColumnsString = 'CREATE table ' + rtrim(@FromShort) + 'Temp (' + ltrim(rtrim(@FromShort)) + '_ForeignKey varchar(30), '
Set @I = 1
WHILE (@I <= @MaxCount)
Begin
If @I <> 1 set @BuildColumnsString = @BuildColumnsString + ', '
Set @BuildColumnsString = @BuildColumnsString + rtrim(@FromShort) + 'Desc' + ltrim(str(@I)) + ' nchar(100), ' + rtrim(@FromShort) + 'Amt' + ltrim(str(@I)) + ' nvarchar(4000)'
Set @I = @I + 1
End
Set @BuildColumnsString = @BuildColumnsString + ')'
Exec(@BuildColumnsString)
Declare @TableIDStep varchar(30), @RecIDStep varchar(30), @RecID varchar(30)
Declare @CurrentFrom varchar(3), @CurrentDesc nchar(100), @CurrentAmt nvarchar(4000)
Declare @InsertStr varchar(5000), @InsertStep int, @CurrentStep varchar(30)

Select @RecIDStep = min(ID) from @PivotTable where FROMTABLE = @FromShort
Set @TableIDStep = 1
Set @InsertStep = 1
while @TableIDStep <= @MaxCount
begin
while @RecIDStep is not null
Begin
select
@RecID = RECORDID,
@CurrentFrom = Fromtable,
@CurrentDesc = replace(DESCRIPTION,char(39),char(39)+char(39)),
@CurrentAmt = AMOUNT
from @PivotTable where FromTable = @FromShort and ID = @RecIDStep

If @CurrentStep <> @RecID Set @InsertStep = 1
Set @CurrentStep = @RecID
Declare @CheckInsertStr nvarchar(100)
Declare @CheckInsertParam nvarchar(100)
Declare @ReturnCount int
select @CheckInsertStr = 'select @ReturnCount = count (*) from ' + ltrim(rtrim(@FromShort)) + 'Temp where ' + ltrim(rtrim(@FromShort)) + '_ForeignKey = ' + @RecID
select @CheckInsertParam = '@ReturnCount int OUTPUT'
exec sp_ExecuteSql @CheckInsertStr, @CheckInsertParam, @ReturnCount output

If @ReturnCount = 0
Set @InsertStr = 'insert into ' + @CurrentFrom + 'Temp (' + ltrim(rtrim(@FromShort)) + '_ForeignKey,' + @CurrentFrom + 'Desc' + ltrim(STR(@InsertStep)) + ',' + @CurrentFrom + 'Amt' + ltrim(STR(@InsertStep)) + ') values (' + CHAR(39) + @RecID + CHAR(39) + ',' + CHAR(39) + ltrim(rtrim(@CurrentDesc)) + CHAR(39) + ',' + char(39) + ltrim(rtrim(isnull(@CurrentAmt, 0.00))) + CHAR(39) + ')'
Else
Set @InsertStr = 'update ' + @CurrentFrom + 'Temp set ' + @CurrentFrom + 'Desc' + ltrim(STR(@InsertStep)) + ' = ' + CHAR(39) + ltrim(rtrim(@CurrentDesc)) + CHAR(39) + ',' + @CurrentFrom + 'Amt' + ltrim(STR(@InsertStep)) + ' = ' + CHAR(39) + ltrim(rtrim(@CurrentAmt)) + CHAR(39) + ' where ' + ltrim(rtrim(@FromShort)) + '_ForeignKey = ' + @RecID


exec(@InsertStr)

update @PivotTable set Processed = 'T' where ID = @RecIDStep
Select @RecIDStep = MIN(ID) from @PivotTable where FROMTABLE = @CurrentFrom and ID <> @RecIDStep and Processed <> 'T'
Set @InsertStep = @InsertStep + 1
End
Set @TableIDStep = @TableIDStep + 1
End

Finally, a second stored procedure is used to drop the temp tables if they already exist, and fill them with values derived from the previous stored procedure:

 CREATE procedure [dbo].[sp_BuildRotatedTempData] as

DECLARE @RC int
DECLARE @DataPart varchar(30)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T1Temp]') AND type in (N'U'))
DROP TABLE [dbo].[T1Temp]
EXECUTE @RC = [dbo].[sp_TablesSnapshot]
'Test1'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T2Temp]') AND type in (N'U'))
DROP TABLE [dbo].[T2Temp]
EXECUTE @RC = [dbo].[sp_TablesSnapshot]
'Test2'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T3Temp]') AND type in (N'U'))
DROP TABLE [dbo].[T3Temp]
EXECUTE @RC = [dbo].[sp_TablesSnapshot]
'Test3'

Now it's just a matter of running the second procedure each time the data changes to get a current snapshot of the data in the current format.  It is then a simple matter of creating a view joining the each of the newly created temp tables to their parent table to return everything in a columnar format.

I hope you find this helpful.  I've attached the scripts to create the Stored Procedures so that you can modify them for your own purposes.

 

Thanks for reading!

 


What's This?
  
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.



Related Content
   Telnet Sample to Send an SMTP email
I am always forgetting how to test sending an email through an SMTP gateway using the Telnet client. 
Posted on Dec 12, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Problem setting SLXTextbox enabled property / Setting unexposed base control properties for a custom control
I recently had an issue with a Quickform where I was attempting to set the Enabled property of a TextBox
Posted on Dec 05, 2014 by Jason Buss to Jason Buss' Blog
 
   Confirming Actions With an "Are you sure?" Prompt in Infor CRM (Saleslogix)
I'm not a huge fan of bugging end users. I figure, if an end user clicked something, he or she meant
Posted on Nov 18, 2014 by Ryan Farley to Ryan Farley's Blog
 
   Adding a report link to the Common Tasks pane in Infor CRM (formerly Saleslogix)
When you are viewing an Entity Detail page, the Common Tasks pane on the right of the page provides a nu
Posted on Nov 12, 2014 by Jason Buss to Jason Buss' Blog
 
   Problem with the Clean Build Folders option in Application Architect
On occasion, it becomes necessary to clean out the build folders and deployed website prior to a build/de
Posted on Sep 16, 2014 by Jason Buss to Jason Buss' Blog
 
Comments

 

Twitter Trackbacks for Advanced Pivoting in MSSQL - Jason Buss' Blog [customerfx.com] on Topsy.com said:

Pingback from  Twitter Trackbacks for                 Advanced Pivoting in MSSQL - Jason Buss' Blog         [customerfx.com]        on Topsy.com

January 6, 2011 11:19 AM

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register