Serendipituos Code

The Problem

A client sent me an excel sheet with financial information classified in columns where each column header was a date and the values associated with that date were listed below

I needed to insert the information to a normalized table with the following columns:

• Client
• Account
• EntryDate
• Value

The Solution

Step 1

Loaded the file into SQL server – created the table: OriginalFile
this table has a columns named after the dates we need to transpose

[Legal Entity] [nvarchar](255) NULL,
[Investor] [nvarchar](255) NULL,
[9/30/95] [float] NULL,
[12/31/95] [float] NULL,
[3/31/96] [float] NULL,
[6/30/96] [float] NULL,
[9/30/96] [float] NULL,
[12/31/96] [float] NULL,
[3/31/97] [float] NULL,
[6/30/97] [float] NULL,
[9/30/97] [float] NULL,
[12/31/97] [float] NULL….

As you can see the name of the column is the date I need

Step 2

Created the table to stored the values in a normalized way:

CREATE TABLE [dbo].[TransposedValuations](
[id] [int] IDENTITY(1,1) NOT NULL,
[Client] [varchar](100) NULL,
[Account] [varchar](100) NULL,
[ValuationDate] [date] NULL,
[Value] [money] NULL,
CONSTRAINT [PK_TransposedValuations] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Step 3

Created a script to read the column names from the table OriginalFile

1. Read the Client, account names
2. Read the column names for those columns named after a date from the sys.columns table
3. Use dynamic SQL to parse the insert statement
4. Execute the parsed statement


DECLARE @legalEntity VARCHAR(100)
DECLARE @investor VARCHAR(100)

-- You need to first read the Client & Account
declare db_values Cursor for
Select [Legal Entity], [Investor] from [OriginalFile]

open db_values
--NOTE: This is also an example on how to select multiple values from a cursor
FETCH NEXT FROM db_values INTO @legalEntity,@investor
WHILE @@FETCH_STATUS = 0
BEGIN

-- Find the defined column names for the table - select only columns that are named after a date
DECLARE db_columns CURSOR for
select Name from sys.columns where object_id = (select object_id from sys.tables Where name = 'OriginalFile') and name not in ('Legal Entity','Investor')

DECLARE @colName VARCHAR(100) -- column name

OPEN db_columns
FETCH NEXT FROM db_columns INTO @colName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sqlString VARCHAR(1000) -- column name

-- Dynamically parse the statement
set @sqlString = 'INSERT INTO [dbo].[TransposedValuations] ([Client],[Account],[Value],[ValuationDate]) '
set @sqlString = @sqlString + ' select [Legal Entity], [Investor], ' + '[' + rtrim(ltrim(@colName)) + '], CONVERT(varchar(10), ''' + rtrim(ltrim(@colName)) + ''', 101)
from [eFrontQuarterlyValues] where [Legal Entity] = ''' + @LegalEntity + ''' and [Investor] = '''+ @Investor + ''''
execute @sqlString
FETCH NEXT FROM db_columns INTO @colName
End

CLOSE db_columns
DEALLOCATE db_columns
FETCH NEXT FROM db_values INTO @LegalEntity,@Investor
end
CLOSE db_values
DEALLOCATE db_values

This script will create the normalized table. Click here for more on data normalization