I'm sure there's a more elegant way to do this but here's one possibility. It involves randomly selecting a number between 0 and 9 using 100 of those 10 numbers distributed according to your chance criteria. For this example, I used the chance percent values as follows:

No. - %

0 - 15

1 - 20

2 - 5

3 - 5

4 - 25

5 - 5

6 - 10

7 - 5

8 - 5

9 - 5

Then in the code, I used the chance percentages to determine how many of those numbers to include in the Substring that selects the random number. The query below will give you the distribution of each of those numbers out of a million records. If you calculate the percentages for each out of the total, you'll see they very closely match the percentages above.

--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

;