12.5.09

Variable as column name in sql server


Problem description:

Am trying to assign the variable value as the column name in the table output.
To overcome this issue, we need to use dynamic queries. Initially, I have assigned the value
into a variable and used in the select statement.

Thanks and Regards,
Venkatesan Prabu .J
at

2 comments:

  1. GilJune 10, 2009 at 10:28 PM

    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'.")


    can you help me on this? thanks in advance... have a nice day


    ----code---------------------

    use db_Treasury
    go

    declare @val01 varchar(10)
    declare @val02 varchar(100)
    set @val01 = 'colum1'
    declare @val11 varchar(10)
    declare @val12 varchar(100)
    set @val11 = 'column2'
    declare @val21 varchar(10)
    declare @val22 varchar(100)
    set @val21 = 'column3'
    declare @val31 varchar(10)
    declare @val32 varchar(100)
    set @val31 = 'column4'
    declare @val41 varchar(10)
    declare @val42 varchar(100)
    set @val41 = 'column5'
    select
    fld_CsAreaCd as Area,
    (fld_CtFName + ' ' + fld_CtLName) as AC,
    fld_csname as CLUSTER
    set @val02 = 'case
    when convert(char(10), fld_CrReqDate, 101) in (06/06/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val01 +' ' ---column1
    exec(@val02)

    set @val12 = 'case
    when convert(char(10), fld_CrReqDate, 101) in (06/07/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val11 +' ' ---column2
    exec(@val12)

    set @val22 = 'case
    when convert(char(10), fld_CrReqDate, 101) in (06/08/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val21 +' ' ---column3
    exec(@val22)

    set @val32 = 'case
    when convert(char(10), fld_CrReqDate, 101) in (06/09/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val31 +' ' ----column4
    exec(@val32)

    set @val42 = 'case
    when convert(char(10), fld_CrReqDate, 101) in (06/10/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val41 +' ' ----column5
    exec(@val42)

    from dbo.tbl_Contacts inner join
    dbo.tbl_Cluster on
    fld_CsClusterCd=fld_CtClusterCd
    left join dbo.tbl_ClCashReq
    on fld_CrClusterCd=fld_CsClusterCd
    and fld_CrStatus >= '30'
    and ((fld_CrReqDate >= '06/06/2009') and (fld_CrReqDate <= '06/10/2009'))
    order by fld_csareacd, fld_CrReqDate asc

    ReplyDelete
  2. GilJune 10, 2009 at 10:31 PM

    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'."

    can you help me on this error? thanks in advace.. have a nice day ahead....


    -----code---



    use db_Treasury
    go

    declare @val01 varchar(10)
    declare @val02 varchar(100)
    set @val01 = 'colum1'
    declare @val11 varchar(10)
    declare @val12 varchar(100)
    set @val11 = 'column2'
    declare @val21 varchar(10)
    declare @val22 varchar(100)
    set @val21 = 'column3'
    declare @val31 varchar(10)
    declare @val32 varchar(100)
    set @val31 = 'column4'
    declare @val41 varchar(10)
    declare @val42 varchar(100)
    set @val41 = 'column5'
    select
    fld_CsAreaCd as Area,
    (fld_CtFName + ' ' + fld_CtLName) as AC,
    fld_csname as CLUSTER
    set @val02 = 'case
    when convert(char(10), fld_CrReqDate, 101) in (06/06/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val01 +' ' ---column1
    exec(@val02)

    set @val12 = 'case
    when convert(char(10), fld_CrReqDate, 101) in (06/07/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val11 +' ' ---column2
    exec(@val12)

    set @val22 = 'case
    when convert(char(10), fld_CrReqDate, 101) in (06/08/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val21 +' ' ---column3
    exec(@val22)

    set @val32 = 'case
    when convert(char(10), fld_CrReqDate, 101) in (06/09/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val31 +' ' ----column4
    exec(@val32)

    set @val42 = 'case
    when convert(char(10), fld_CrReqDate, 101) in (06/10/2009) then LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
    CONVERT(DATETIME, fld_CrReqDate), 22), 3)) end as '+ @val41 +' ' ----column5
    exec(@val42)

    from dbo.tbl_Contacts inner join
    dbo.tbl_Cluster on
    fld_CsClusterCd=fld_CtClusterCd
    left join dbo.tbl_ClCashReq
    on fld_CrClusterCd=fld_CsClusterCd
    and fld_CrStatus >= '30'
    and ((fld_CrReqDate >= '06/06/2009') and (fld_CrReqDate <= '06/10/2009'))
    order by fld_csareacd, fld_CrReqDate asc

    ReplyDelete