Post reply

use dynamic table whiile dynamic pivot

  • September 27, 2016 at 10:13 am

    #317855

    Hi,

    I want to use different table name every time while transpose data in my dynamic query

    DECLARE @colsUnpivot AS NVARCHAR(MAX),

    @TableName NVarchar(MAX),

    @query AS NVARCHAR(MAX),

    @colsPivot as NVARCHAR(MAX),

    @Table_Name as Varchar(200),

    @fiscal_year as Varchar(4)

    SET @Table_Name = 'opd_lkp_scholar'

    SET @fiscal_year = '2015'

    select @colspivot = stuff((select ','+C.code_name from @Table_Name as C

    where C.fiscal_year = @fiscal_year

    for xml path('')), 1, 1, '')

    PRINT @colspivot

    It says

    "Msg 1087, Level 16, State 1, Line 11

    Must declare the table variable "@Table_Name"." But I have already declared the variable.

    Can anyone please help with this code? Thanks in advance.

  • September 27, 2016 at 10:22 am

    #1903404

    Dynamic table names in queries require the use of dynamic SQL.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • September 27, 2016 at 3:18 pm

    #1903492

    Agreed. You have to substitute the table name into your string and then execute it.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • September 27, 2016 at 3:20 pm

    #1903493

    Thanks all for the reply. I got it.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply