tag:blogger.com,1999:blog-6357505892638015674.post362980154150855658..comments2023-12-11T22:43:08.539-08:00Comments on Welcome to SQLServer MVP Blog: Variable as column name in sql serverUnknownnoreply@blogger.comBlogger2125tag:blogger.com,1999:blog-6357505892638015674.post-81100765304291739912009-06-10T22:31:25.073-07:002009-06-10T22:31:25.073-07:00i want my column names to be variable.. i have cod...i want my column names to be variable.. i have code (see below) but a got an error in sql server 2005 saying "Incorrect syntax near the keyword 'from'."<br /><br />can you help me on this error? thanks in advace.. have a nice day ahead....<br /><br /><br />-----code---<br /><br /><br /><br />use db_Treasury<br />go<br /><br />declare @val01 varchar(10)<br />declare @val02 varchar(100)<br />set @val01 = 'colum1'<br />declare @val11 varchar(10)<br />declare @val12 varchar(100)<br />set @val11 = 'column2'<br />declare @val21 varchar(10)<br />declare @val22 varchar(100)<br />set @val21 = 'column3'<br />declare @val31 varchar(10)<br />declare @val32 varchar(100)<br />set @val31 = 'column4'<br />declare @val41 varchar(10)<br />declare @val42 varchar(100)<br />set @val41 = 'column5'<br />select<br />fld_CsAreaCd as Area,<br />(fld_CtFName + ' ' + fld_CtLName) as AC,<br />fld_csname as CLUSTER<br />set @val02 = 'case<br />when convert(char(10), fld_CrReqDate, 101) in (06/06/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val01 +' ' ---column1<br />exec(@val02)<br /><br />set @val12 = 'case<br />when convert(char(10), fld_CrReqDate, 101) in (06/07/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val11 +' ' ---column2<br />exec(@val12)<br /><br />set @val22 = 'case<br />when convert(char(10), fld_CrReqDate, 101) in (06/08/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val21 +' ' ---column3<br />exec(@val22)<br /><br />set @val32 = 'case<br />when convert(char(10), fld_CrReqDate, 101) in (06/09/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val31 +' ' ----column4<br />exec(@val32)<br /><br />set @val42 = 'case<br />when convert(char(10), fld_CrReqDate, 101) in (06/10/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val41 +' ' ----column5<br />exec(@val42)<br /><br />from dbo.tbl_Contacts inner join<br />dbo.tbl_Cluster on<br />fld_CsClusterCd=fld_CtClusterCd<br />left join dbo.tbl_ClCashReq <br />on fld_CrClusterCd=fld_CsClusterCd<br />and fld_CrStatus >= '30'<br />and ((fld_CrReqDate >= '06/06/2009') and (fld_CrReqDate <= '06/10/2009'))<br />order by fld_csareacd, fld_CrReqDate ascGilhttps://www.blogger.com/profile/13170951176812884794noreply@blogger.comtag:blogger.com,1999:blog-6357505892638015674.post-29530782716938823922009-06-10T22:28:12.617-07:002009-06-10T22:28:12.617-07:00hi, i to need to make my column name variable, i h...hi, i to need to make my column name variable, i have code (see below) but i got error ("Incorrect syntax near the keyword 'from'.")<br /><br /><br />can you help me on this? thanks in advance... have a nice day<br /><br /><br />----code---------------------<br /><br />use db_Treasury<br />go<br /><br />declare @val01 varchar(10)<br />declare @val02 varchar(100)<br />set @val01 = 'colum1'<br />declare @val11 varchar(10)<br />declare @val12 varchar(100)<br />set @val11 = 'column2'<br />declare @val21 varchar(10)<br />declare @val22 varchar(100)<br />set @val21 = 'column3'<br />declare @val31 varchar(10)<br />declare @val32 varchar(100)<br />set @val31 = 'column4'<br />declare @val41 varchar(10)<br />declare @val42 varchar(100)<br />set @val41 = 'column5'<br />select<br />fld_CsAreaCd as Area,<br />(fld_CtFName + ' ' + fld_CtLName) as AC,<br />fld_csname as CLUSTER<br />set @val02 = 'case<br />when convert(char(10), fld_CrReqDate, 101) in (06/06/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val01 +' ' ---column1<br />exec(@val02)<br /><br />set @val12 = 'case<br />when convert(char(10), fld_CrReqDate, 101) in (06/07/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val11 +' ' ---column2<br />exec(@val12)<br /><br />set @val22 = 'case<br />when convert(char(10), fld_CrReqDate, 101) in (06/08/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val21 +' ' ---column3<br />exec(@val22)<br /><br />set @val32 = 'case<br />when convert(char(10), fld_CrReqDate, 101) in (06/09/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val31 +' ' ----column4<br />exec(@val32)<br /><br />set @val42 = 'case<br />when convert(char(10), fld_CrReqDate, 101) in (06/10/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), <br />CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val41 +' ' ----column5<br />exec(@val42)<br /><br />from dbo.tbl_Contacts inner join<br />dbo.tbl_Cluster on<br />fld_CsClusterCd=fld_CtClusterCd<br />left join dbo.tbl_ClCashReq <br />on fld_CrClusterCd=fld_CsClusterCd<br />and fld_CrStatus >= '30'<br />and ((fld_CrReqDate >= '06/06/2009') and (fld_CrReqDate <= '06/10/2009'))<br />order by fld_csareacd, fld_CrReqDate ascGilhttps://www.blogger.com/profile/13170951176812884794noreply@blogger.com