asked May 01 '12 at 09:34 PM in Default
all you have to do to put more columns in is add the column names to the start of the CTE
then add the extra columns to the cte query
Then for your main select statement, add the columns to that
To change the name of the Case statement add as 'RevenueStreamID' after the END
Read [this] to find out about CTE's.
The row_number function basically sort the query and adds sequential numbers to the query. It is the best way to solve problems like yours. http://msdn.microsoft.com/en-us/library/ms186734.aspx
The case statement simply checks when the revenuestreamID is equal to 7, and changes it to the next value.
good luck!: h ttp://msdn.microsoft.com/en-us/library/ms190766.aspx
answered May 02 '12 at 11:13 PM
I ran the code that you suggested me to run and it works! I modified it a bit:
is there a unique identifier in the table? to get this to work properly, you would need to have a column you can order by , then you could extract the revenueStreamID with a case statement. And use a CTE with a row_number function to get consecutive row numbers. (The unique order in the sample below is ID).
Then you can put the results in a temp table, or where ever you like. Just don't use a loop!
answered May 02 '12 at 12:05 AM