DECLARE @RandomNumber float
DECLARE @RandomInteger int
DECLARE @MaxValue int
DECLARE @MinValue int
 
SET @MaxValue = 9
SET @MinValue = 0
 
SELECT @RandomNumber = RAND()
 
SELECT @RandomInteger = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValue
SELECT @RandomInteger
 

--Build cteTally with 1M rows
With E1(N)
As
(
	 Select 1 Union All Select 1 Union All Select 1 Union All 
	 Select 1 Union All Select 1 Union All Select 1 Union All 
	 Select 1 Union All Select 1 Union All Select 1 Union All Select 1
),                       
E2(N)
As 
(
	Select 1 
	From 
		E1 a, E1 b
),
E4(N)
As 
(
	Select 1 
	From E2 a, E2 b
), 
E6(N)
As 
(
	Select 1 
	From E4 a, E2 b
),
cteTally(N) 
As 
( 
	 Select Row_Number() Over (Order By (Select Null)) From E6
),
--Do the select for the numbers from 0-9 based on the requested distribution
chance
As
(
	Select
		Substring('0000000000000001111111111111111111122222333334444444444444444444444444555556666666666777778888899999', Cast(Floor((Rand(Checksum(Newid()))*100) + 1) as tinyint),1) as test_group
	From
		cteTally
)

--Check the results
Select
	test_group,
	Count(test_group) as total
From
	chance
Group by test_group
Order by test_group
;

;WITH chances(RES, CHANCE) AS (
	-- Range of values to generate and chance of being picked.
	-- This is not a percentage but odds.  eg 'You' has a 20 in 81 chance
		SELECT RES, CHANCE 
		FROM (
			VALUES 
			('Me', 2 ),
			('You', 20),
			('Them', 18),
			('Others', 1 ),
			('Everyone', 30),
			('Nobody', 10)
			) AS a(RES, CHANCE)
	),
	-- Create a table of ranges for the results
	chancerange(RES,rLow,rHigh)  AS (
		SELECT a.RES, CAST(SUM(ISNULL(b.chance,0)) AS FLOAT) rLow, CAST(SUM(ISNULL(b.chance,0)) + a.Chance AS FLOAT) rHigh
		FROM chances a
			LEFT OUTER JOIN Chances b ON b.RES < a.RES
		GROUP BY a.RES, a.Chance
	),
	-- Generate a random number for each record in the target table
	randomNum AS (
		SELECT RAND(Checksum(Newid())) * c.T SeedResult, a.*
		FROM Tally a -- REPLACE WITH TABLE TO GENERATE NUMBERS FOR 
			CROSS APPLY (SELECT SUM(CHANCE) T FROM Chances) c
	),
	-- Build the results
	randomResults AS (
		SELECT (SELECT RES FROM chancerange WHERE SeedResult BETWEEN rLow AND rHigh) RandomResult, * 
		FROM randomNum	
	)
SELECT RandomResult, count(*) num, cast(count(*) / 10000.00 as decimal(4,2)) pct 
FROM randomResults 
GROUP BY RandomResult