A few months back I wrote a web service that generates lottery numbers for the Texas Lottery. The concept is that the numbers are not truly random; otherwise, there would be a uniform spread for the numbers. Also, some numbers are hot numbers and you are more likely to pick a winner using the hottest numbers.
The numbers from the txlottery site are in the order they came up in the drawing. The numbers have no sort to them; they are random.
What I needed was to be able to return the results to the user in ascending order (smallest to largest). So our task is to take the random lottery numbers and reorder them so that they are sorted from left to right and output as follows:
Let’s start by creating a table variable and populating it with data from txlottery.org. Since I have already done that for you just copy the following and run it to see the number spread.
DECLARE @LotteryResults TABLE
(
ID INT IDENTITY(1,1),
Ball1 INT,
Ball2 INT,
Ball3 INT,
Ball4 INT,
Ball5 INT
)
INSERT INTO @LotteryResults
SELECT 21,7,16,44,9 UNION ALL
SELECT 14,33,5,8,21 UNION ALL
SELECT 48,15,2,10,37 UNION ALL
SELECT 16,37,45,20,12 UNION ALL
SELECT 5,11,22,28,8 UNION ALL
SELECT 9,41,6,48,12 UNION ALL
SELECT 36,30,29,8,1 UNION ALL
SELECT 15,23,45,18,42 UNION ALL
SELECT 2,3,5,18,39
DECLARE @tmp TABLE
(
Id INT,
Val INT
)
SELECT * FROM @LotteryResults
Now that we have our data we are ready to create the actual SQL that’s going to order the data from left to right. We will be using PIVOT and UNPIVOT in conjunction with ROW_NUMBER to turn the table on it’s side, order the numbers and then turn the table back right side up. It may sound confusing but it is actually quite elegant. Some of the purists will claim slow performance when there is a huge amount of data and I might agree with them if I actually cared about performance for this solution. When I need it to perform then maybe I will make a change but a few thousand rows are transformed for me in less than a second so why do I care.
In the last half of the query we want to PIVOT our data and perform MIN(Number) on our set. This will output the ordering we want. If we wanted our data to in Descending order then we could just change that to MAX(Number) and we are good.
SELECT Id, [1],[2],[3],[4],[5]
FROM
(
SELECT Id,
Number,
ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Number) AS RowNum
FROM
(
SELECT ID, Ball1, Ball2, Ball3, Ball4, Ball5
FROM @LotteryResults
) p
UNPIVOT
(
Number FOR Val IN (Ball1, Ball2, Ball3, Ball4, Ball5)
)AS unpvt
) p
PIVOT
(
MIN(Number) FOR RowNum IN ([1],[2],[3],[4],[5])
) AS PVT
This is a clever use of several built-in SQL Server functions. I originally did it a different way us case statements all over the place and it was terrible. I decided I didn’t like how it was being done and wanted a better way. I think this way is very cool and hopefully you do as well.