CODEWITHSUNDEEP
sqlsql-server
Shyam Pandian
Shyam Pandian

Reputation: 45

How to call columns dynamically in SQL Server?

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?

Upvotes: 0

Views: 1346

Answers (2)

Sean Lange
Sean Lange

Reputation: 33571

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.

Upvotes: 3

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

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.

Upvotes: 0

Related Questions