Search code examples
sqlsql-serverdata-conversion

Converting data file to flatfile?

I'm looking to convert a data file to a flat file format, with multiple hierarchical dimensions. I have included an example, but ideally, I will have an unknown number of columns that I wish to transform, while the hierarchical dimensions will be fixed.

1

Solution

  • If you have unknown or variable columns, you can dynamically UNPIVOT your data without using Dynamic SQL. Note that we only need to exclude the two key columns ... Where [key] not in ('Country','City')

    Example

    Select Country
      ,City
      ,Metric = B.[key]
      ,Value  = B.Value 
 From  YourTable A
 Cross Apply ( Select * 
                From  OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper  )  ) 
                Where [key] not in ('Country','City')
             ) B

    Returns

    Country City    Metric      Value
US      NY      Snowfall    13
US      NY      Temp        94
US      NY      Snowfall    5
US      NY      Temp        84
UK      London  Snowfall    6
UK      London  Temp        85