I have GDP data for 1960 - 2020, each year is stored as a column. In order to unpivot the columns, do I have to hard code all year(columns)?

For example

Desired(unpivot)

But doing this for 1960- 2020, is it necessary to state each year columns in my unpivot statement?

I'm attempting to use dynamic query and at a very starting point, I have

DECLARE @DynamicSQL VARCHAR(MAX)
DECLARE @year INT = 1960


SET @DynamicSQL = 'SELECT GDP.['   CAST(@year AS VARCHAR(10))  '] FROM GDP'


EXEC(@DynamicSQL)

But how can I increment 1 year and add the list of years in one SET statement?

Thanks!

CodePudding user response：

Seems like you're mainly asking about how to loop through the years. The code below should do it. All that's left would be to use this in conjunction with the UNPIVOT statement to fully solve the problem. You can find a neat example here: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

DECLARE @DynamicSQL VARCHAR(MAX)
DECLARE @yearStart INT = 1960
DECLARE @yearEnd INT = 2020
DECLARE @yearIterator INT = @yearStart
DECLARE @fieldPrefix String = 'GDP.[' --where GDP is the table you want to UNPIVOT
DECLARE @fieldSuffix String = ']'

SET @DynamicSQL = 'SELECT '   @fieldPrefix 

while 1=1
begin

   SET @DynamicSQL = @DynamicSQL   @yearIterator   @fieldSuffix

   if @yearIterator < @yearEnd
   begin

      SET @DynamicSQL = @DynamicSQL   ', '

   end 

   SET @yearIterator = @yearIterator   1

   if @yearIterator > @yearEnd
   begin

      SET @DynamicSQL = @DynamicSQL   ' FROM GDP'
      break

   end 

end 

EXEC(@DynamicSQL)

CodePudding user response：

Here is an approach which will dynamically UNPIVOT your data without actually using Dynamic SQL

Example or dbFiddle

Select A.[country name]
      ,B.*
 From YourTable A
 Cross Apply (
                Select [Key]
                      ,Value
                 From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                 Where [Key] not in ('country name','OtherColums','ToExclude')
             ) B

Results

country name    Key     Value
US              1960    200
US              1961    400
CANADA          1960    300
CANADA          1961    400