Assign year variable to column alias for excel sheet

Question

I have an excel sheet which is bound to a stored procedure. In the stored procedure I am selecting the columns that appear in the excel sheet. Now I'm facing an issue when I wanted to add some more columns:

Some value 2016
Some value 2017
Some value 2018

The first column is adding the actual year to the header and the two others the next one and the year after the next one.

My problem is that I don't know how to do this dynamically. I've tried something like this:

DECLARE @actualYear INT = YEAR(GETDATE())

SELECT tab.Name,
    myTable.SomeValue [Some value @actualYear],
    myTableNext.SomeValue [Some value @actualYear+1],
    myTableAfterTheNext.SomeValue [Some value @actualYear+2]
FROM SomeTable tab
LEFT JOIN MyTable myTable ON tab.SomeId = myTable.SomeId
    AND myTable.[Year] = @actualYear
LEFT JOIN MyTable myTableNext ON tab.SomeId = myTableNext.SomeId
    AND myTable.[Year] = (@actualYear+1)
LEFT JOIN MyTable myTableAfterTheNext ON tab.SomeId = myTableAfterTheNext.SomeId
    AND myTable.[Year] = (@actualYear+2)

The output is the following:

+------+------------------------+--------------------------+--------------------------+
| Name | Some value @actualYear | Some value @actualYear+1 | Some value @actualYear+2 |
+------+------------------------+--------------------------+--------------------------+

Second try:

SELECT tab.Name,
    myTable.SomeValue ['Some value' + @actualYear]
    ...

Output:

+------+----------------------------+ ...
| Name | 'Some value' + @actualYear | ...
+------+----------------------------+ ...

How can I get the correct column headers dynamically?

Ralph · Accepted Answer · 2016-09-29T14:22:24.447

You'll have to create a dynamic sql query like so (short example):

declare @i int;
declare @sql nvarchar(max);

set @i = 2016;
set @sql = N'select 1 as [' + cast(@i as nvarchar) + N']';

exec(@sql);

Translated to your sql query this should be something like this:

declare @sql nvarchar(max);
declare @actualYear int = year(getdate());

set @sql = @sql + N'select  tab.Name, '
set @sql = @sql + N'        myTable.SomeValue [' + cast(@actualYear as nvarchar) + N'], '
set @sql = @sql + N'        myTableNext.SomeValue [' + cast(@actualYear + 1 as nvarchar) + N'], '
set @sql = @sql + N'        myTableAfterTheNext.SomeValue [' + cast(@actualYear + 2 as nvarchar) + N'] '
set @sql = @sql + N'from    SomeTable tab '
set @sql = @sql + N'left join MyTable myTable '
set @sql = @sql + N'on      tab.SomeId = myTable.SomeId '
set @sql = @sql + N'        and myTable.Year = @actualYear '
set @sql = @sql + N'left join MyTable myTableNext '
set @sql = @sql + N'on      tab.SomeId = myTableNext.SomeId '
set @sql = @sql + N'        and myTable.Year = (@actualYear + 1) '
set @sql = @sql + N'left join MyTable myTableAfterTheNext '
set @sql = @sql + N'on      tab.SomeId = myTableAfterTheNext.SomeId '
set @sql = @sql + N'        and myTable.Year = (@actualYear + 2); '

exec(@sql);

How to easily convert an SQL query into a dynamic SQL query:

Note, within Notepad++ you should replace the regular expression ^(.*)$ with set @sql = @sql + N'\1 '.

Update

Possible implementation of the above into a stored procedure (short example only):

IF OBJECT_ID('procTest', 'P') IS NOT NULL
    DROP PROCEDURE procTest;
GO

CREATE PROCEDURE procTest
AS
BEGIN
    DECLARE @i INT;
    DECLARE @sql NVARCHAR(MAX);

    SET @i = 2016;
    SET @sql
        = N'insert into #t (Column1) VALUES (' + CAST(@i AS NVARCHAR)
          + N'); ' + N'insert into #t (Column1) '
          + N'SELECT cast(1 as nvarchar) as [' + CAST(@i AS NVARCHAR) + N']';

    EXEC (@sql);
END;
GO

CREATE TABLE #t
(
    Column1 NVARCHAR(MAX)
);

EXEC dbo.procTest;

SELECT *
FROM #t;

DROP TABLE #t;

Now I'm having the problem that my stored procedure doesn't return any columns. The result is `Command(s) completed successfully.`. However, I can't save the results in a temporary table because I have dynamic table aliases... What should I do now? — diiN__________, Sep 29 '16 at 13:30
Whenever your answer is something like `OK, but now...` you can be almost certain that you are asking a new question. Well, here is the answer to that new / additional problem: http://stackoverflow.com/a/4626369/1153513 — Ralph, Sep 29 '16 at 13:47
So, I'll ask a new question. The link does not answer the additional question. I can't create a temporary table because of my dynamic table aliases. I'd have to declare the fields of the temp table what I'm not able to. — diiN__________, Sep 29 '16 at 13:56
Sorry, my mistake. I didn't quite understand the new problem at once. The problem is now to create a dynamic table with a dynamic result. I am not quite sure if this is possible. The only solution I can think of would be to insert into a temp table with `nvarchar(max)` for each column the entire resulting table **including the column name**. Then the temp table could contain in the first row the column header and in the remaining rows the result-set. Of course this would be a very dirty solution. Yet, that's all I can think of. — Ralph, Sep 29 '16 at 14:19
Can't you just change the Excel sheet to pull a query instead of a stored procedure? Like this you could put the dynamic SQL query into the sheet only and not into the stored procedure. Here is a brief screen-cast on how to change to an SQL query (instead of a table or a stored procedure) in Excel: http://stackoverflow.com/a/37896889/1153513 Just note that this requires the SQL to start with `set nocount on;` otherwise sql queries don't work in Excel. — Ralph, Sep 29 '16 at 14:50

Assign year variable to column alias for excel sheet

1 Answers1

Update