Selecting a Column dynamically

• nigelc

  Old Hand

  Points: 380

  More actions

  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

• Phil Parkin

  SSC Guru

  Points: 245813

  More actions

  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.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• John Mitchell-245523

  SSC Guru

  Points: 148786

  More actions

  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

• Luis Cazares

  SSC Guru

  Points: 183686

  More actions

  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

• nigelc

  Old Hand

  Points: 380

  More actions

  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