How to call columns dynamically in SQL Server?

Question

Please find below the script. I am trying to call the column names dynamically. The first 3 columns are LOC, Product and status are fixed. There are 53 columns atfer that from 2015 and for 2016 like: 1-2015, 2-2015, 3-2015 ... 53-2015 and 1-2016, 2-2016, 3-2016 ... 53-2016. I want to build dynamic SQL to call all that columns. I am doing this because I want it in specific order which i'm not able to get if i use SELECT * FORM TABLE.

declare @values varchar(max)

declare @n varchar(10) 

set @n = 1

set @values = '
   SELECT[LOC]
   ,[PRODUCT]
   ,[STATUS]
   ,['+@n+'-2015]
   ,['+@n+'-2016]
FROM [LGI_Temp].[dbo].[Temp_PIVOT]';
exec(@values);

This gives me result like:

LOC     PRODUCT     STATUS  1-2015    1-2016

I want it for all the 53 values in each year.

How to do that?

You could create a stored procedure and use dynamic-SQL. http://stackoverflow.com/questions/10092869/can-i-pass-column-name-as-input-parameter-in-sql-stored-procedure — Gaston G, Aug 03 '15 at 15:12

score 3 · Accepted Answer · answered Aug 03 '15 at 15:41

Using a tally table is a better approach here than looping. Sure this loop is small and not likely to cause a lot of performance problems but the tally table is so simple it should be used here. My personal favorite article explaining tally tables can be found over at sql server central. http://www.sqlservercentral.com/articles/T-SQL/62867/

Taking the code that Bogdan Bogdanov posted here is how you would convert that to using a tally table instead of a loop.

First you need the tally table. I do this in my system with a view.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally
GO

Now that it is a view I don't have to worry about writing it again. Just use it.

DECLARE @SqlCmd VARCHAR(MAX);

SET @SqlCmd = 'SELECT [LOC], [PRODUCT], [STATUS] ';

select @SqlCmd = @SqlCmd + ', [' + cast(N as varchar(2)) + '-2015], [' + cast(N as varchar(2)) + '-2016]'
from cteTally 
where N <= 53

SET @SqlCmd = @SqlCmd + ' FROM [LGI_Temp].[dbo].[Temp_PIVOT];'

select @SqlCmd

The bigger issue here as I see it is that this table is horrible denormalized. Instead of a column for each month you should have a date column...although from the name this looks to be a permanent "temp" table used for a pivot. There are better ways of dynamically converting rows to columns. Around this site people like the Dynamic Pivot. I personally prefer a dynamic cross tab but that is a preference thing. I find the syntax for a cross tab less obtuse and there is even a slight performance benefit from it.

I, too, prefer [dynamic crosstab](http://www.sqlservercentral.com/articles/Crosstab/65048/) instead of dynamic pivot. Hah, seems like we both learn from Jeff. ;) — Felix Pamittan, Aug 04 '15 at 14:21

Bogdan Bogdanov · Answer 2 · 2015-08-03T15:27:39.637

0

Ok, try this:

DECLARE @SqlCmd VARCHAR(MAX);
DECLARE @colN VARCHAR(10); 

SET @SqlCmd = 'SELECT [LOC], [PRODUCT], [STATUS] ';
SET @colN = 1;

WHILE (@colN < 54) 
BEGIN 
    SET @SqlCmd = @SqlCmd + ',[' + @colN + '-2015],[' + @colN + '-2016]';
    SET @colN = @colN  + 1;
END;

SET @SqlCmd = @SqlCmd + ' FROM [LGI_Temp].[dbo].[Temp_PIVOT];'

EXEC (@SqlCmd);

REMARK: This is not the best solution. I just try to show an quick example. See comment of @Sean Lange bellow.

edited Aug 03 '15 at 15:27

answered Aug 03 '15 at 15:10

Bogdan Bogdanov

1,707
2
20
31

I write that he can not do that because there was missing `'`. And it looks like he want to call directly select + string concat. – Bogdan Bogdanov Aug 03 '15 at 15:20
I didn't downvote but using a loop here is horribly inefficient. This is an excellent opportunity for a tally or numbers table. Here is my personal favorite article on the topic. http://www.sqlservercentral.com/articles/T-SQL/62867/ – Sean Lange Aug 03 '15 at 15:25
I agree, @Sean Lange, just want to update my answer with an exmaple. I also want to show him quick solution. May be he need to call just an ad-hoc query. – Bogdan Bogdanov Aug 03 '15 at 15:26
Assuming you have a tally table already, the tally table solution is quicker both from a code writing position and performance of the query. – Sean Lange Aug 04 '15 at 14:28
I agree, @Sean Lange - I just provided very quick alternative for ad-hoc cases. If he needs that in production `tally table` is better. – Bogdan Bogdanov Aug 04 '15 at 14:30

How to call columns dynamically in SQL Server?

2 Answers2