dynamic create table based on excel columns

Question

I have a csv file as shown below

accont| prod|Item |Tag |LDESC|may13|jun13|jul13|jun16
a |b |c | d | s | 20 | 20.3|30.2 |34
x |y |z | c | s | 21 | 23.3|30.4 |35

Every month we are getting a file like this, but have no idea as how many months will be present in that file.

Here the requirement is that every month I have to load data into the database and transpose the month column into the row dynamically.

Any help will be much appreciated.

Answer 1

Here is one solution (explanation inside the comments to code):

USE tempdb
--here we declare variables
DECLARE @string nvarchar(max),  --this will store csv data as a single string
        @xml xml,               --here is a variable that will hold @string converted in xml
        @table_create nvarchar(max), --holds xsxript to drop and create temporary table
        @columns nvarchar(max), --will hold the columns like [may13],[jun13],[jul13],[jun16]
        @sql nvarchar(max)      --here will be generated query with unpivot

--Load csv into a variable
SELECT @string = BulkColumn
FROM OPENROWSET(BULK N'D:\test.csv', SINGLE_CLOB) AS Document

--convert it to xml (we need only first row to get column headers
SELECT @xml =CAST('<row>'+'<col>'+REPLACE(SUBSTRING(@string,1,CHARINDEX(CHAR(10),@string)),'|','</col><col>')+'</col>'+'</row>' as xml)

--generate a table creating script
SELECT @table_create = N'
    USE tempdb; 
    IF (OBJECT_ID(N''##tempCSVload''))IS NOT NULL DROP TABLE ##tempCSVload; 
    CREATE TABLE ##tempCSVload ('+STUFF(@table_create,LEN(@table_create),1,')') +';'

EXEC sp_executesql @table_create

--Now we shall BULK INSERT into temp table
BULK INSERT ##tempCSVload
FROM  N'D:\test.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = '|',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

--The number and names of monthYY columns are unknown so we take them from sys.columns table
SELECT @columns = ISNULL(@columns,'') + ','+QUOTENAME(name) 
FROM sys.columns
WHERE [OBJECT_ID] = OBJECT_ID(N'##tempCSVload')
AND column_id > 5 --First 5 are skipped as there are not part of pivoting

--Generate the UNPIVOT script
SELECT @sql = N'
SELECT *
FROM ##tempCSVload
UNPIVOT (
    [Values] FOR [Dates] IN ('+STUFF(@columns,1,1,'')+')
) as unpvt'

--And EXECUTE
EXEC sp_executesql @sql

Output:

accont  prod    Item    Tag     LDESC   Values  Dates
a       b       c       d       s        20     may13
a       b       c       d       s        20.3   jun13
a       b       c       d       s       30.2    jul13
a       b       c       d       s       34      jun16
x       y       z       c       s        21     may13
x       y       z       c       s        23.3   jun13
x       y       z       c       s       30.4    jul13
x       y       z       c       s       35      jun16

You can add INSERT INTO YourTable to SELECT @sql = N' like:

SELECT @sql = N'
INSERT INTO YourTable
SELECT *
FROM ##tempCSVload
UNPIVOT (
    [Values] FOR [Dates] IN ('+STUFF(@columns,1,1,'')+')
) as unpvt'

To insert data right in table with fixed columns and datatypes. Note that [Values] is stored as nvarchar(max) and night have spaces, so better use CAST([Values] as float) like:

SELECT @sql = N'
SELECT  accont,
        prod,
        Item,
        Tag,
        LDESC,
        CAST([Values] as float) as [Values],
        [Dates]
FROM ##tempCSVload
UNPIVOT (
    [Values] FOR [Dates] IN ('+STUFF(@columns,1,1,'')+')
) as unpvt'