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
   Using a Test Plan
So you have approved the Statement of Work (SOW) for some modifications and are awaiting the delivery of
Posted on Mar 13, 2014 by George Jensen to George Jensen's Blog
 
   Setting the Defaults on the Insert Account/Contact Duplicate Record View
When creating a new Account/Contact record in Saleslogix web, you have the ability to check for duplicat
Posted on Mar 10, 2014 by Jason Buss to Jason Buss' Blog
 
   Creating a Saleslogix Ticket Pretty Prefix and Suffix in External Applications
Some entities in SalesLogix, such as Tickets, use a "pretty key" as an easier to use value to r
Posted on Feb 28, 2014 by Ryan Farley to Ryan Farley's Blog
 
   Adding string formatting to values using WebEntityBinding
In a recent project, I needed to round numeric values displayed in the Opportunity Snapshot.  This
Posted on Feb 05, 2014 by Jason Buss to Jason Buss' Blog
 
   SQL Stored Procedure to Create SalesLogix Table ID Values
Most of the SalesLogix systems I see now days no longer use remote users. Instead, all users VPN into a c
Posted on Dec 23, 2013 by Ryan Farley to Ryan Farley's 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