how to set column name dynamically?

HI chaps

my scenario is that, i have a table in which i have column such as col01,col02,col03....col31

i want to retrieve the records related to that column by specifying the name dynamically (by using loop) as fist three character are same ('col?') and i have to do integer increment in last two character (?01...?31). Is it possible that I can use variable for column name?, if yes how? or is there any other way to achieve this task ?

waiting for your reply

regards

Something like the following will work for you:

create table DynCols
(col01 int,
col02 int,
col11 int,
col12 int) -- and so on, fo all 30 columns

declare @.query varchar(255)
set @.query = 'select '

declare @.counter int
set @.counter = 1

while @.counter <= 31
begin
if @.counter < 10
set @.query = @.query + 'col' + '0' + convert(char(1), @.counter) + ','
else
set @.query = @.query + 'col' + convert(char(2), @.counter) + ','
set @.counter = @.counter + 1
end

-- remove the last comma and append the rest:
set @.query = substring(@.query, 0, len(@.query) - 1) + ' from DynCols'

print @.query

The result is:

select col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col3 from DynCols

|||

fafnir wrote:

Something like the following will work for you:

create table DynCols
(col01 int,
col02 int,
col11 int,
col12 int) -- and so on, fo all 30 columns

declare @.query varchar(255)
set @.query = 'select '

declare @.counter int
set @.counter = 1

while @.counter <= 31
begin
if @.counter < 10
set @.query = @.query + 'col' + '0' + convert(char(1), @.counter) + ','
else
set @.query = @.query + 'col' + convert(char(2), @.counter) + ','
set @.counter = @.counter + 1
end

-- remove the last comma and append the rest:
set @.query = substring(@.query, 0, len(@.query) - 1) + ' from DynCols'

print @.query

The result is:

select col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col3 from DynCols

thanx for your reply....

yes i can do it that way but still it will be string (varchar) can you tell me how to execute that statement? i can print the statement but i dont know how will I able to execute a query which is stored in the varaible

|||

sp_executesql @.query

|||

i have written the follwoing script

DECLARE @.CURRENTDATE DATETIME

DECLARE @.STARTDATE DATETIME

DECLARE @.LOOPDATE DATETIME

DECLARE @.NOOFMONTHS INT

DECLARE @.NOOFDAYS INT

DECLARE @.DAYCOUNTER TINYINT

DECLARE @.I INT

DECLARE @.J TINYINT

DECLARE @.DAYNUMBER VARCHAR(6)

/*INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR(BPCODE)

SELECT DISTINCT LTRIM(RTRIM(CZMMCU)) FROM STAGING.DBO.F0007*/

SET @.CURRENTDATE = (SELECT CONVERT(DATETIME,CAST(DATE AS VARCHAR(10))) FROM PRESENTATIONEUROPE.DBO.CALENDAR

WHERE CURRENTDAY = 0 )

--Check for first time execution and set start date accordingly

IF (SELECT MAX(DATE) FROM PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR) IS NOT NULL

SET @.STARTDATE = CONVERT(DATETIME,(SELECT CONVERT(VARCHAR(8),MAX(DATE)) FROM

PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR))

ELSE

SET @.STARTDATE = (SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4),(CAST(MIN(CZYR)AS INT) + 2000))+'-'+

CONVERT(VARCHAR(2),MIN(CZMT))+'-01') FROM STAGING.DBO.F0007 WHERE CZYR = (SELECT MIN(CZYR) FROM STAGING.DBO.F0007))

--GET THE NO OF MONTH DIFFERENCE

SET @.NOOFMONTHS = DATEDIFF(MM,@.STARTDATE,@.CURRENTDATE)

SET @.I = 1

SET @.LOOPDATE = @.STARTDATE

DECLARE @.LOOPDATE2 INT

DECLARE @.QUERY VARCHAR(500)

WHILE (@.I<=@.NOOFMONTHS)

BEGIN

SET @.LOOPDATE2 = (CONVERT(VARCHAR(8),CONVERT(DATETIME, CONVERT(VARCHAR(4),YEAR(@.LOOPDATE))

+'-'+ CONVERT(VARCHAR(2),MONTH(@.LOOPDATE))+'-01'),112))

SET @.J = 1

WHILE (@.J<=31)

BEGIN

IF (@.J<10)

BEGIN

SET @.QUERY = 'INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR (BPCODE, DATE, DATEE1, WORKINGDAY ) SELECT LTRIM(RTRIM(CZMMCU)), @.LOOPDATE2, (SELECT DATEE1 FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE DATE ='+CONVERT(VARCHAR(8),@.LOOPDATE,112)+'),

CZTD0'+CONVERT(CHAR(1),@.J)+' FROM STAGING.DBO.F0007 T1 LEFT OUTER JOIN PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR T2

ON LTRIM (RTRIM(T1.CZMMCU)) = T2.BPCODE AND T2.DATE =' +CONVERT(VARCHAR(8),@.LOOPDATE2)+

'WHERE T2.BPCODE IS NULL AND CZYR='+ SUBSTRING(CONVERT(VARCHAR(4),YEAR(@.LOOPDATE)),3,4)+

'AND CZMT ='+CONVERT(CHAR(2),MONTH(@.LOOPDATE))

END

ELSE

BEGIN

SET @.QUERY = 'INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR (BPCODE, DATE, DATEE1, WORKINGDAY ) SELECT LTRIM(RTRIM(CZMMCU)), @.LOOPDATE2, (SELECT DATEE1 FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE DATE ='+ CONVERT(VARCHAR(8),@.LOOPDATE,112) +'),

CZTD'+CONVERT(CHAR(1),@.J)+' FROM STAGING.DBO.F0007 T1 LEFT OUTER JOIN PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR T2

ON LTRIM (RTRIM(T1.CZMMCU)) = T2.BPCODE AND T2.DATE =' +CONVERT(VARCHAR(8),@.LOOPDATE2)+

'WHERE T2.BPCODE IS NULL AND CZYR='+ SUBSTRING(CONVERT(VARCHAR(4),YEAR(@.LOOPDATE)),3,4)+

'AND CZMT ='+CONVERT(CHAR(2),MONTH(@.LOOPDATE))

END

SP_EXECUTESQL @.QUERY

SET @.J = @.J+1

END

PRINT @.LOOPDATE

SET @.I = @.I + 1

END

and getting follwing error

Msg 102, Level 15, State 1, Line 62

Incorrect syntax near 'SP_EXECUTESQL'.

|||

use:

exec sp_executesql @.query