Table & Column Name (alias) dynamically using dynamic SQL

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 10, 2012 at 10:54 am

  #251095

  Hi SQL Experts,

  I'm trying to automate a rather complicated process.

  The process compare numbers of the previous year or any given year to the current year data.

  The following a simplified example of the SELECT Statement that I want to generate dynamically:

  SELECTTOP 10 Loss2010.AcctNo,Loss2010.AcctgYear AS AcctgYear2010,Loss2011.AcctgYear AS AcctgYear2011,

  Loss2010.AcctgMonth AS AcctgMonth2010,Loss2011.AcctgMonth AS AcctgMonth2011,

  Loss2010.LossDate AS LossDate2010,Loss2011.LossDate AS LossDate2011

  FROMLoss AS Loss2010

  INNER JOINLoss AS Loss2011 ON Loss2010.AcctNo = Loss2011.AcctNo

  The DDL:

  CREATE TABLE Loss (

  AcctgYear CHAR(4) NULL,

  AcctgMonth CHAR(2) NULL,

  AcctNo VARCHAR(20) NULL,

  LossDate VARCHAR(20)

  )

  Sample Data Load:

  INSERT INTO Loss (AcctgYear,AcctgMonth,AcctNo, LossDate)

  SELECT 2010,12,'CTX100006915',1100101

  UNION ALL

  SELECT 2010,12,'CTX100006916',1100101

  UNION ALL

  SELECT 2010,12,'CTX100006917',1100103

  UNION ALL

  SELECT 2010,12,'CTX100006917',1100103

  UNION ALL

  SELECT 2010,12,'CTX100006919',1100102

  UNION ALL

  SELECT 2010,12,'CTX100006923',1100103

  UNION ALL

  SELECT 2010,12,'CTX100006923',1100103

  UNION ALL

  SELECT 2010,12,'CTX100006924',1100101

  UNION ALL

  SELECT 2010,12,'CTX100006926',1100104

  UNION ALL

  SELECT 2010,12,'CTX100006927',1100103

  UNION ALL

  SELECT 2011,12,'CTX100006915',1100101

  UNION ALL

  SELECT 2011,12,'CTX100006916',1100101

  UNION ALL

  SELECT 2011,12,'CTX100006917',1100103

  UNION ALL

  SELECT 2011,12,'CTX100006917',1100103

  UNION ALL

  SELECT 2011,12,'CTX100006919',1100102

  UNION ALL

  SELECT 2011,12,'CTX100006923',1100103

  UNION ALL

  SELECT 2011,12,'CTX100006923',1100103

  UNION ALL

  SELECT 2011,12,'CTX100006924',1100101

  UNION ALL

  SELECT 2011,12,'CTX100006926',1100104

  UNION ALL

  SELECT 2011,12,'CTX100006927',1100103

  UNION ALL

  SELECT 2011,12,'CTX100006923',1100103

  UNION ALL

  SELECT 2011,12,'CTX100006923',1100103

  UNION ALL

  SELECT 2011,12,'CTX100006917',1100103

  UNION ALL

  SELECT 2011,12,'CTX100006917',1100103

  Sample output:

  AcctNoAcctgYear2010AcctgYear2011AcctgMonth2010AcctgMonth2011LossDate2010LossDate2011

  CTX10000691520102010121211001011100101

  CTX10000691520112010121211001011100101

  CTX10000691620102010121211001011100101

  CTX10000691620112010121211001011100101

  CTX10000691720102010121211001031100103

  CTX10000691720102010121211001031100103

  CTX10000691720112010121211001031100103

  CTX10000691720112010121211001031100103

  CTX10000691720112010121211001031100103

  CTX10000691720112010121211001031100103

  Could someone please tell me how I could make the Table & Column Aliases and qualifier Dynamic?

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  January 10, 2012 at 11:06 am

  #1431313

  What's the question?

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 10, 2012 at 11:30 am

  #1431329

  You're trying to make your PIVOT dynamic, right?

  Then just use dynamic SQL. In your example, the variable that determines the number and content of columns is the year: query distinct year in advance and build a dynamic sql with all the years.

  -- Gianluca Sartori

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  January 10, 2012 at 11:44 am

  #1431339

  here's a post that may give some ideas (J Moden)

  http://www.sqlservercentral.com/Forums/Topic1220939-391-1.aspx#bm1223418

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 10, 2012 at 2:02 pm

  #1431402

  Gianluca Sartori (1/10/2012)

  ---

  You're trying to make your PIVOT dynamic, right?

  Then just use dynamic SQL. In your example, the variable that determines the number and content of columns is the year: query distinct year in advance and build a dynamic sql with all the years.

  Sorry it is not a PIVOT

  I did not include the Financial Columns but basically I want to compare Total Incurred and Outstanding Balance for 2010 and 2011 but I need it for 2011 and 2009, etc.

  Outstanding Balance is a key comparison.

  I will also need to compare Months DEC 2011 to Nov 2011.

  I will eventually populate a number of spreadsheets and write some VBA code to consolidate, move around, format,etc.

  Thank you for your reply. 🙂

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/