Increment Column Name 1

[databanker]

I am trying to figure out how to increment a column name in a select statement. Basically I want one select statement to run against my 12 monthly balance fields (EOMB). The below code is what I have so far. Any advice is greatly appreciated.

declare @counter int
set @counter = 0
while @counter < 12

begin
set @counter = @counter + 1
SELECT CAST(@counter AS char) AS Month, @counter AS Balance, 'EOMB' AS [Measure Type], [Account Number], Branch
FROM [Import - CY AVG Balances]

end

[Import - CY AVG Balances]

[Account Number]
Branch
[1 EOMB]
[2 EOMB]
[3 EOMB]
[4 EOMB]
[5 EOMB]
[6 EOMB]
[7 EOMB]
[8 EOMB]
[9 EOMB]
[10 EOMB]
[11 EOMB]
[12 EOMB]

 

[ESquared]

Have 12 monthly balance fields is really not the right design. I'm afraid I can't bring myself to help you any further than that: such a design is NOT the way to go.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[databanker]

I agree! This is an import from a legacy system. I am building a new table that replaces rows for columns. Unfortunately, I have to extract monthly balance fields as individual columns thanks to my progress data source.

 

[mrdenny]

If this is SQL 2005 you can use the UNPIVOT command to do this in a single command.

Otherwise you'll need 12 select statements (unless you decided that you like working with dynamic SQL).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[databanker]

This database is running on a 2000 box. I am trying to clean up what is currently 12 SQL statements. Dynamic SQL is what I was trying to pull off in my above example. UNPIVOT sounds very useful... wish I could use it.

 

[ESquared]

The classic 12-times union:

select
   [Account Number]
   Branch
   Month,
   EOMB = [1 EOMB]
from
   [Import - CY AVG Balances] B
union all 
select
   [Account Number]
   Branch
   Month,
   [2 EOMB]
from
   [Import - CY AVG Balances] B
...
union all
select
   [Account Number]
   Branch
   Month,
   EOMB = [12 EOMB]
from
   [Import - CY AVG Balances] B

Or my own technique I've developed and use in production systems, which is much easier to use and modify, even if you still have to put all 12 column names in manually. You can build this query with dynamic SQL if you absolutely must:

select
   [Account Number]
   Branch
   Month,
   EOMB = 
      case Month
         when 1 then [1 EOMB]
         when 2 then [2 EOMB]
         when 3 then [3 EOMB]
         ...
         when 11 then [11 EOMB]
         when 12 then [12 EOMB]
      end
from
   [Import - CY AVG Balances] B
   CROSS JOIN (
      select Month = 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12
   ) M

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[databanker]

Wow - this works perfectly.. quick too (Method 2)!

Nice solution.

 

[ESquared]

Indeed... why hit the table 12 times when you can hit it once? It's bound to be faster.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

and sorry about the missing commas

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]