Looping through columns SQL to select multiple years

user_19164973 0
Viewed 54

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

country name 1960 1961
US 200 400
CANADA 300 400

Desired(unpivot)

countryname value Year
US 200 1960
US 400 1961
CANADA 300 1960
CANADA 400 1961

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!

tsql sql-server sql
2 Answers

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)
user_48382160

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
user_15700000
Related