Post reply

Selecting a Column dynamically

  • March 7, 2017 at 12:41 am

    #341457

    I Have a table with the following columns
    Business Unit,
    AprTarget,
    AugTarget ,
    DecTarget,
    Etc

    Depending on the month we are in want to select the column with the appropriate month. So, if I ran the query today (Being March) I would want to pull back the column MarTarget.

    I was hoping to use something like DARTPART and concatenating it with the string “Target” but realised it doesn’t return the month as text (was thinking of the excel function). Am I on the right track with this?

    I do have a date table which has two columns which looks like
    Month Number, Month Text
    3, Mar

    What would be the simplest way of bringing back the right column.

    Thanks

  • March 7, 2017 at 2:57 am

    #1931995

    And what happens next year? Do you just update these columns?

    A better design for this table would be (Business Unit, Target date, Target), where target date is 1st of the month.

    Doing lookups on this table is easy and non-dynamic.

    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

  • March 7, 2017 at 3:07 am

    #1931996

    Phil is right - it would be a lot easier you you could change to a normalised table design.  If you're stuck with what you have, though, you can do something like this:
    DECLARE
         @Month char(3)
    ,    @SQL varchar(4000);

    SELECT @Month = MonthText
    FROM MyDateTable
    WHERE MonthNumber = DATEPART(MONTH,GETDATE());

    SET @sql = 'SELECT ' + @Month + 'Target
        FROM BusinessTable';

    EXEC (@SQL);

    John

  • March 7, 2017 at 6:19 am

    #1932019

    I'd prefer if you stay out of dynamic SQL for this. Here's another option.
    SELECT CASE MONTH(GETDATE())
       WHEN 1 THEN JanTarget
       WHEN 2 THEN FebTarget
       WHEN 3 THEN MarTarget
       WHEN 4 THEN AprTarget
       WHEN 5 THEN MayTarget
      END

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 7, 2017 at 1:02 pm

    #1932151

    Thanks Luis. This works well for what I'm trying to achieve
    Phil to answer your question the targets would be updated in the table automatically as i am extracting from CRM

    Thanks for everyone's help

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

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