Convert repetaing fields to relational

[Mmattson]

I thought I saw this problem solved before, but couldn't find it in by searching.

I have all the megamillion lottery winning numbers. I have them broken up into their positions (1st, 2nd, 3rd, 4th, 5th, MoneyBall). I want to take the flat record and turn it into a relational so I can do some statistical analysis on it.

If anyone knows of a function that already is written please let me know
-Thanks, Marcus

Example:

I have this....

ID 1st 2nd 3rd 4th 5th MB
1 3 12 15 20 25 37


I want this...

ID Number
1 3
1 12
1 15
1 20
1 25
1 37

Thanks for your help.

 

[stopher]

Hi
Use UNION to append each column...

SELECT ID, [1st] AS LotNum
FROM LotteryNumbers
UNION
SELECT ID, [2nd] AS LotNum
FROM LotteryNumbers
UNION
SELECT ID, [3rd] AS LotNum
FROM LotteryNumbers
.
.
etc

hth
Stopher

 

[Mmattson]

Worked beautifully, as expected. Much obliged.

-cheers