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
