Getting values from a table that's inside a table (unpivot / cross apply)

Question

I'm having a serious problem with one of my import tables. I've imported an Excel file to a SQL Server table. The table ImportExcelFile now looks like this (simplified):

+----------+-------------------+-----------+------------+--------+--------+-----+---------+
| ImportId | Excelfile         | SheetName | Field1     | Field2 | Field3 | ... | Field10 |
+----------+-------------------+-----------+------------+--------+--------+-----+---------+
|    1     | C:\Temp\Test.xlsx | Sheet1    | Age / Year | 2010   | 2011   |     | 2018    |
|    2     | C:\Temp\Test.xlsx | Sheet1    | 0          | Value1 | Value2 |     | Value9  |
|    3     | C:\Temp\Test.xlsx | Sheet1    | 1          | Value1 | Value2 |     | Value9  |
|    4     | C:\Temp\Test.xlsx | Sheet1    | 2          | Value1 | Value2 |     | Value9  |
|    5     | C:\Temp\Test.xlsx | Sheet1    | 3          | Value1 | Value2 |     | Value9  |
|    6     | C:\Temp\Test.xlsx | Sheet1    | 4          | Value1 | Value2 |     | Value9  |
|    7     | C:\Temp\Test.xlsx | Sheet1    | 5          | NULL   | NULL   |     | NULL    |
+----------+-------------------+-----------+------------+--------+--------+-----+---------+

I now want to insert those values from Field1 to Field10 to the table AgeYear(in my original table there are about 70 columns and 120 rows). The first row (Age / Year, 2010, 2011, ...) is the header row. The column Field1 is the leading column. I want to save the values in the following format:

+-----------+-----+------+--------+
| SheetName | Age | Year | Value  |
+-----------+-----+------+--------+
| Sheet1    | 0   | 2010 | Value1 |
| Sheet1    | 0   | 2011 | Value2 |
| ...       | ... | ...  | ...    |
| Sheet1    | 0   | 2018 | Value9 |
| Sheet1    | 1   | 2010 | Value1 |
| Sheet1    | 1   | 2011 | Value2 |
| ...       | ... | ...  | ...    |
| Sheet1    | 1   | 2018 | Value9 |
| ...       | ... | ...  | ...    |
+-----------+-----+------+--------+

I've tried the following query:

DECLARE @sql NVARCHAR(MAX) =
    ';WITH cte AS
     (
         SELECT i.SheetName,
             ROW_NUMBER() OVER(PARTITION BY i.SheetName ORDER BY i.SheetName) AS rn,
             ' + @columns + ' -- @columns = 'Field1, Field2, Field3, Field4, ...'
         FROM dbo.ImportExcelFile i
         WHERE i.Sheetname LIKE ''Sheet1''
     )
     SELECT SheetName,
            age Age,
            y.[Year]
     FROM cte
     CROSS APPLY
     (
         SELECT Field1 age
         FROM dbo.ImportExcelFile
         WHERE SheetName LIKE ''Sheet1''
         AND ISNUMERIC(Field1) = 1
     ) a (age)
     UNPIVOT
     (
         [Year] FOR [Years] IN (' + @columns + ')
     ) y
     WHERE rn = 1'

EXEC (@sql)

So far I'm getting the desired ages and years. My problem is that I don't know how I could get the values. With UNPIVOT I don't get the NULL values. Instead it fills the whole table with the same values even if they are NULL in the source table.

Could you please help me?

Answer 1

Perhaps an alternative approach. This is not dynamic, but with the help of a CROSS APPLY and a JOIN...

The drawback is that you'll have to define the 70 fields.

Example

;with cte0 as (
                Select A.ImportId
                      ,A.SheetName
                      ,Age = A.Field1
                      ,B.*
                 From ImportExcelFile A
                 Cross Apply ( values ('Field2',Field2)
                                     ,('Field3',Field3)
                                     ,('Field10',Field10)
                             ) B (Item,Value)

              )
     ,cte1 as ( Select * from cte0 where ImportId=1 )
 Select A.SheetName
       ,[Age]   = try_convert(int,A.Age)
       ,[Year]  = try_convert(int,B.Value)
       ,[Value] = A.Value
  From  cte0 A
  Join cte1 B on A.Item=B.Item
  Where A.ImportId>1

Returns