Starting with a list of downtime codes and their associated descriptions, I made up another table that had two columns for each code: a duration in minutes, and a bit field indicating whether this particular downtime halted production. Often if a production line is down, several pieces of equipment are fixed or adjusted, so adding them up doesn't give a correct perspective on how much time production overall had stopped.

Such a table is trivial to maintain in Access, since there is a column per code, but quite messy once it is time to return the columns back to a 'row' structure. In short, for a given batch or product run, I need to have a group of records that identifies a downtime code, an interval of time, and status indicating whether this was the cause of a production stoppage.

After exploring a number of avenues, the solution I came up with involves extracting the column names from the table, two at a time, formatting those two names into an 'Insert / Select' statement, and running this using the system stored procedure 'sp_executesql'. In short, I define a stored procedure that composes a set of over 150 SQL statements, running them across the column definitions two at a time.

The downtime code is in a format nn.nn. The column names in the table are expressed as CnnSnn_aaaaaaaaaaaaaa where aaaaaaaaaaa... is the description. Example column names might be C25S30_PowerOutageInterval, and C25S30_PowerOutageHalt, respectively.

What I need is the product batch, the downtime code, the interval, and whether this downtime code is responsible for a stoppage. These four columns are stored in the table named DowntimeExtraction.

CREATE PROCEDURE ExtractRepackDowntimes AS

declare @DurationColumn as varchar(255);
declare @HaltColumn as varchar(255);
declare @statement as nvarchar(255);
declare @Counter as int;

truncate table DowntimeExtraction;

set @Counter = 2;

while @Counter < 353
begin

select top 1 @DurationColumn = col_name(Object_ID('BatchDowntime'), @Counter), @HaltColumn = col_name(Object_ID('BatchDowntime'), @Counter + 1) from BatchDowntime;

select @statement = 'Insert into Production.dbo.DowntimeExtraction Select BatchNumber, ''' + substring(@DurationColumn, 2, 2) + '.' + substring(@DurationColumn, 5, 2) + ''' as DowntimeCode, ' + @DurationColumn +', ' + @HaltColumn + ' from Production.dbo.BatchDowntime;';

select @statement as SQLString;

exec master.dbo.sp_executesql @stmt = @statement;

set @Counter = @Counter + 2;

end;

delete from DowntimeExtraction where Duration is null;

delete from DowntimeExtraction where ProductionHalt is null;

delete from DowntimeExtraction where ProductionHalt = 0;

The 'master.dbo....' and 'Production.dbo....' prefixes are absolutely required. Most SQL operations that are limited to one database don't need the database name and owner prefixes. In this particular case none of this worked unless I supplied the database and owner names.

The normal presentation format (indentations, etc.) of the SQL involves some effort to reproduce in HTML, so that isn't done here.

The following view 'goes the other way', meaning that the columnar data is assigned to associated columns in the row structure. The table DowntimeCode has a column in it containing the column name of each associated downtime code.

SELECT 'update r set r.' + BatchDowntimeColumnName + 'Duration = e.MinutesDowntime,
r.' + BatchDowntimeColumnName + 'Halt = 0
from BatchDowntime r, DownTimeEvents e where e.DowntimeCode = '''
+ SUBSTRING(BatchDowntimeColumnName, 2, 2) + '.' + SUBSTRING(BatchDowntimeColumnName, 5, 2)
+ ''' and r.BatchNumber = e.DowntimeBatchNumber;' AS UpdateSQL FROM DowntimeCode;

This view generates one SQL update statement for every column. The output from this view can be pasted into Query Analyzer and executed. If this needs to be run 'on demand', then a number of approaches can be used, but using the abovementioned sp_executesql stored procedure should be OK.