How to use a FOR Loop in Sequence Column??

[sconti11]

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!

 

[gmmastros]

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

 

[markros]

The simplest solution for such problem is to use tally (or Numbers) table.

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

 

[sconti11]

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?

 

[gmmastros]

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.

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

 

[sconti11]

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!

 

[gmmastros]

I assume that Amt_01 represents January (month 1) and so on...

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

 

[fredericofonseca]

assuming that Amt_01 represents January (month 1) and so on...

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886