SQL - Nested Columns possible?

Question

I'm fairly au fait with SQL but there is one thing I'm not sure is possible so I thought I'd give it a go asking another queston on here.

I have the need to return "Countries" as each row, and then have 3 columns for each Country which records an average of percentages from another table. This is fine, I can do this without problem.

The place I am stuck is that, I want the 3 columns per row to be repeated for, let's say, each month. So, effectively, I want the 3 columns to be nested under a higher level column which contains the month name. The layout I need is below (the column "Index" is a calculation of the percentage in the Company1/2 columns)

      |         January            | 

Country|Company 1|Company 2|Index|

Is this even possible?

Answer 1

You can achieve this sort of thing with dynamic SQL, just for example:

CREATE TABLE #Example (Country varchar(20))

DECLARE @sql nvarchar(max)
DECLARE @prefix nvarchar(100)
DECLARE @startMonth int=3
DECLARE @endMonth int=6

DECLARE @currentMonth int=@startMonth

WHILE @currentMonth<@endMonth
BEGIN
  SET @prefix='Month'+CAST(@currentMonth AS varchar(10))
  SET @sql='ALTER TABLE #Example ADD'
  +'['+@prefix+'_Company1] varchar(20)'
  +',['+@prefix+'_Company2] varchar(20)'
  +',['+@prefix+'_Index] int'
  EXEC (@sql)

  SET @sql='...' -- ... SQL to set values into the prefixed columns based upon your criteria
  EXEC (@sql)

  SET @currentMonth=@currentMonth+1
END

select * from #Example -- The result with 3 columns per month

This will add 3 columns for each month, each with a unique prefix from the @currentMonth value

Maybe this technique gives you a basis for a solution?