Posts tagged: dynamic sql

The ever illusive Dynamic Pivot

By , April 10, 2010 17:47

Introduction
Recently, I have read many forum entries made by frustrated SQL users who have had problems dynamically building a list of columns and rows when the number of these is variable. I have also read, in the same forums, the argument that this sort of functionality belongs in the reporting application and not at the database level. Although that makes perfect sense, there are situations in which there just is no reporting application available or, as weird as it may seem, the reporting services built in to MSSQL Server since version 2005 are explicitly not to be implemented. These are the typical day to day problems many of us are faced with in our jobs.

Some time ago I was faced with exactly this problem and was required to build a “report” for a customer who needed a list of ordered part numbers and their required quantities for any given day.  This at first sounded like a simple select and group by, but it later turned out that what the customer wanted was a list with part numbers as column names, dates as row names and the accumulated quantities of parts per date to be the body of information.

At the time, I was unable to find any useful information in the usual forums (although I generally try to find a solution without help from outside, this doesn’t always work), so this is what I came up with.

Let’s go for it
First let’s do a bit of DDL. Ordinarily there would be a series of other tables along with FK relationships between the tables as well as a range of primary/unique keys and indexes. For the sake of simplicity, I am not going to reproduce the entire range of DDL here.

use ThinkTank
go

if  OBJECT_ID('tempdb..#PartNumbers') is not null
 drop table #PartNumbers
go
create table #PartNumbers (
 [ID] int identity (1,1),
 [PartNo] varchar(35),
 --[PartUsage] etc...
 )
go

if  OBJECT_ID('tempdb..#VehicleOrders') is not null
 drop table #VehicleOrders
go
create table #VehicleOrders (
 [ID] int identity(1,1),
 [ProdNr] varchar(35),
 [DeliveryDate] datetime,
 --[OrderStatus] etc...
 )
go

if  OBJECT_ID('tempdb..#VehicleOrdersItem') is not null
 drop table #VehicleOrdersItem
go
create table #VehicleOrdersItem (
 [VehicleOrders_ID] varchar(35),
 [PartNo_ID] int
 ) 
go

Now for the data

--let's work with 10 part numbers
insert into #PartNumbers (PartNo)
values
('AW93 54502B98 ADWAMN'),
('AW93 54502B98 ADWNUG'),
('AW93 54502B98 AF0AMN'),
('AW93 54502B98 AF8NUG'),
('AW93 54502B98 AG0AMN'),
('AW93 54502B98 AG8NUG'),
('AW93 54502B98 BD0AMN'),
('AW93 54502B98 BDWAMN'),
('AW93 54502B98 BDWNUG'),
('AW93 54502B98 BF0AMN')
go

--Each vehicle can consist of 1 - n parts.
insert into #VehicleOrders (ProdNr, DeliveryDate)
 select top 150000
  --Random production number
  'P1021' + cast(dbo.ufn_getRandomInt(100000,250000) as Varchar(35)),
  --add random number of days to date so that we have a 10 day range
  DATEADD(dd, dbo.ufn_getRandomTinyint(1,10), GETDATE())
  from ufn_MillionTally()
go

insert into #VehicleOrdersItem( VehicleOrders_ID, PartNo_ID)
 select top 1000000
  --Random production number id between 1 and 150000
  dbo.ufn_getRandomint(1,150000),
  --random id from #PartNumbers we want to avoid partno. 7
  replace(dbo.ufn_getRandomTinyint(1,10), 7, 1)
  from ufn_MillionTally()
go

The core problem here is that there could be any number of part numbers (this is an ever changing list in the automobile industry) and the number of dates is also a variable ( No Mo.-Fri.: trade union meetings, public holidays, strikes, extra shifts etc…). Furthermore, there could be part numbers that have not yet been ordered or have been discontinued, but are still in the master data table #PartNumbers.

This was my first simple select and group by mentioned earlier.

--we only want the date info, time is not relevant for this report
select CAST(vo.DeliveryDate as date)as [Date],
  pn.PartNo,
  count(vi.PartNo_ID) as Quantity   
  from #VehicleOrdersItem vi inner join #VehicleOrders vo
 on vo.ID = vi.VehicleOrders_ID
  inner join #PartNumbers pn
 on pn.ID = vi.PartNo_ID   
  group by CAST(vo.DeliveryDate as date), pn.PartNo
order by  [Date], pn.PartNo
go

Essentially, the information returned is what was required, but not formatted in a way that is easy to use.

What was required was the information from the above select, but formatted in such a way, that each date is cumulated into a single row and the part numbers are the names of the columns. As it is impossible to predict what and when the customer is going to order, both of these are variable (well the “what” is predictable to a certain degree, but I don’t want to delve into the ins and outs of suppliers’ nightmares in the automobile industry). Thus it is not possible to define a fixed set of column and row names.

Enter the dynamic pivot!

I used dynamic T-SQL to build the actual PIVOT statement, so that the variables could be inserted in a separate work-step and executed at the end.

declare @PartNoList nvarchar(max) = ''
declare @PartNoListIsNull nvarchar(max) = ''
declare @PivotString nvarchar(max)  =
'select cast(DeliveryDate as date) as [Date of Usage], ?FieldListIsnull   
 from   
 (   
  select cast(vo.DeliveryDate as date) as [DeliveryDate],
   pn.PartNo, isnull(count(*),0) as Quantity   
   from #VehicleOrdersItem vi inner join #VehicleOrders vo
 on vo.ID = vi.VehicleOrders_ID
   inner join #PartNumbers pn
 on pn.ID = vi.PartNo_ID   
   group by CAST(vo.DeliveryDate as date), pn.PartNo
 ) as RSet
 pivot
 (
  sum(RSet.Quantity)   
  for RSet.PartNo in   
   (
    ?FieldList   
   )
 ) as PTable
 order by DeliveryDate'

Build a list of part numbers:

--@PartNoListIsNull is used to display the part numbers and is required
--to allow for part numbers that have no orders within the viewed time frame.
--Remember, we avoided orders for PartNo_ID = 7 while building test data earlier.
select @PartNoListIsNull =
  @PartNoListIsNull + 'ISNULL(' + QUOTENAME(PartNo) + ', 0) as '
  + QUOTENAME(PartNo) + ', '    
 from #PartNumbers   
order by PartNo   

select @PartNoList =
  @PartNoList + QUOTENAME(PartNo) + ', '    
 from #PartNumbers   
order by PartNo  

--now we cut off the trailing comma
select  @PartNoListIsNull =
  SUBSTRING(@PartNoListIsNull, 1, LEN(@PartNoListIsNull) - 1),
@PartNoList =
  SUBSTRING(@PartNoList, 1, LEN(@PartNoList) - 1)

Replace the variables in the PIVOT command and execute to get the required result:

select	@PivotString = REPLACE(@PivotString, '?FieldListIsnull', @PartNoListIsNull),
		@PivotString = REPLACE(@PivotString, '?FieldList', @PartNoList)
execute  sp_executesql @PivotString

There are surely better ways of doing this, but it did work for me and fulfilled the customer’s requirements. I have yet to investigate wether or not this sort of code would be prone to SQL injection, but I don’t think so because this code, packed into a stored procedure, would have a typed parameter of type datetime which, as far as I am aware, would not allow malicious code to be injected.

Credit where credit is due:

1. The random function used above was extracted from Eric Fickes’ Blog. Thanks!

2. I wrote the ufn_MillionTally() tally function after closely following an interesting editorial and the resulting discussion on SQLServerCentral.com
This could also be replaced by a simple cross join on the master.sys.all_columns table.(See code download below)

Code Download
The code is provided as is and should not be run in a production environment before sufficient testing has taken place. Feel free to use this solution if you see fit.

Finally
Since having the idea to write this article I have briefly read about cross table queries, but haven’t had a chance to compare these approaches to my solution yet. I will do so in the not too distant future and will report on my findings.

Panorama Theme by Themocracy