Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to use a FOR Loop in Sequence Column??

Status
Not open for further replies.

sconti11

Technical User
Jan 31, 2011
95
US
My title is a mouthful..i know. But please bear with me.

I am trying to figure out the best way to go about creating a column that needs to be created based on the number of rows in another column.

Here is what I mean..I have a column titled YearID, and there can be between 1-12 rows for each year ( you probably guessed it), this will represent the months. So I need to create another column next to the YearID to capture the Month. So if there are 4 rows for 2004, I need the MonthID column to show 1-4, and so on...

So I was thinking about looping through the YearID column, and as long as the year is the same then show a value depending on the number of rows...but I am not sure if this is the best way...it definitely is not the simpliest right now!

Any suggestions would be helpful!
 
Can you show some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
The simplest solution for such problem is to use tally (or Numbers) table.
Code:
select T.*, N.Number as Month from myTable T
inner join dbo.Numbers N on N.Number between 1 and T.NumberOfMonths

PluralSight Learning Library
 
Ok...here is a sample of what I am talking about...hopefully it comes out ok:

Amt | Unit | ProjectID | Year |

30 | 5 | 213 | 2005 |
789 | 15 | 213 | 2005 |
435 | 53 | 214 | 2005 |
30 | 9 | 214 | 2005 |
30 | 91 | 214 | 2005 |

So in the example above, I would be adding another column called MonthID ...and this is how I need the output to look:

Amt | Unit | ProjectID | YearID | MonthID

30 | 5 | 213 | 2005 | 1
789 | 15 | 213 | 2005 | 2
435 | 53 | 214 | 2005 | 1
30 | 9 | 214 | 2005 | 2
30 | 91 | 214 | 2005 | 3

So each row, grouped by the ProjectID and Year, literally becomes the monthid..

Does that help?
 
Yes, that helps, but there is still a question remaining...

In your sample data where projectid = 214 and yearid = 2005, how do you know which one should be assigned the monthid of 1, 2 or 3?

With SQL Server 2005 and newer, there is a Row_Number function that you can use so that you do not need to write a loop to determine the MonthID value.

Code:
Select  *,
        Row_Number() Over (Partition By ProjectID, Year Order By [!]SomeColumn[/!])
From    [!]YourTableNameHere[/!]

Notice the [!]SomeColumn[/!] highlighted in red. You need to put a column name in place of SomeColumn. This column will dictate which row is assigned 1, 2, 3 etc....



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Ok...I think I am getting closer to a solution..to add more context to this already confusing issue...the original data that I am working with contains 12 columns of Amt data, one column for each month of a year, and the same number of columns for Units.

So the columns are like this:

Amt_01 | Amt_02 | Amt_03 | Unit_01 | Unit_02 | Unit_03


So I was writing a Stored Procedure to UNPIVOT those columns into rows, so that I will only have 2 columns...one for Amt and one for Unit.

But this now brings me to where I am in the story..I have to show the month for those rows that were transformed from columns..

As clear as mud...right?

Anyway, I believe I should be able to use the Row_Number function...will have to see if that does the trick!
 
I assume that Amt_01 represents January (month 1) and so on...

Code:
Select ProjectId, Year, Amt_01 As Amt, Unit_01 As Unit, 1 As MonthId
From   YourTable

Union All

Select ProjectId, Year, Amt_02, Unit_02, 2 As MonthId
From   YourTable

Union All 

Select ProjectId, Year, Amt_03, Unit_03, 3 As MonthId
From   YourTable

etc....


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
assuming that Amt_01 represents January (month 1) and so on...
Code:
SELECT ProjectId
      ,Year
      ,CASE monthid
		   WHEN 1 THEN Amt_01
		   WHEN 2 THEN Amt_02
		   WHEN 3 THEN Amt_03
		   WHEN 4 THEN Amt_04
		   WHEN 5 THEN Amt_05
		   WHEN 6 THEN Amt_06
		   WHEN 7 THEN Amt_07
		   WHEN 8 THEN Amt_08
		   WHEN 9 THEN Amt_09
		   WHEN 10 THEN Amt_10
		   WHEN 11 THEN Amt_11
		   WHEN 12 THEN Amt_12
       END AS Amt
      ,CASE monthid
		   WHEN 1 THEN Unit_01
		   WHEN 2 THEN Unit_02
		   WHEN 3 THEN Unit_03
		   WHEN 4 THEN Unit_04
		   WHEN 5 THEN Unit_05
		   WHEN 6 THEN Unit_06
		   WHEN 7 THEN Unit_07
		   WHEN 8 THEN Unit_08
		   WHEN 9 THEN Unit_09
		   WHEN 10 THEN Unit_10
		   WHEN 11 THEN Unit_11
		   WHEN 12 THEN Unit_12
       END AS Amt      
      ,monthid
FROM YourTable
cross JOIN 
(SELECT TOP 12 ROW_NUMBER() over (order BY ob.object_id) as monthid from sys.objects ob
) t

I prefer this method to the union all

if you have a numbers table it should be used instead of the sys.objects method.
in that case it would be "SELECT TOP 12 mynumber as monthid from numbers_table order by mynumber"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top