Divide The Table Data Randomly Based On Percentages

I have a table called Employees, having 100 records. The columns are ID and Name. 100 is just a sample number, it can have any number of records like 15 or 115. The sampling should

Solution 1:

Answer is similar to those form Michal and his answer is also correct, however NTILE to be used as alternative, since it will split a dataset to 100 equal chunks. ROW_Number will not work for a case for a dataset with a number of rows smaller than 100.:

select id,a.name,
       casewhen rn <=65then'group 1'elsecasewhen rn <=85then'group 2'else'group 3'endendfrom
(
    --newid() will generate random order of recordsselect ID, name , NTILE(100) OVER (ORDERBY NEWID()) [rn] from dbo.Employees
) [a]

Solution 2:

Following query contains inner query, which will introduce row numbers, based on random order, generated by newid(). Then it's enough to assing in outer query group 1 for rows between 1 and 65 (which is 65% of 100), group 2 for row numbers between 66 and 85 (20%) and group 3 for the rest (15%).

select id,name,
       casewhen rn <=65then'group 1'elsecasewhen rn <=85then'group 2'else'group 3'endendfrom
(
    --newid() will generate random order of recordsselect ID, name, ROW_NUMBER() over (orderby newid()) [rn] from Employees
) [a]

Solution 3:

You can try the following query

DECLARE@EmployeeTABLE (ID INT,Name VARCHAR(100))

DECLARE@numRowsint,@iintSET@numRows=100SET@i=1

WHILE @i<@numRowsBEGININSERT@Employee(ID,Name) SELECT@i,'Test Name'+Convert(VARCHAR(10),@i)
    SET@i=@i+1ENDSELECT A.ID,A.Name,(CASEWHEN A.RowNumber BETWEEN1AND65THEN'Group1'WHEN A.RowNumber BETWEEN66AND85THEN'Group2'WHEN A.RowNUmber BETWEEN86AND100THEN'Group3'ELSE''END) 'Group'FROM
(SELECTROW_NUMBER() OVER(ORDERBY ID) AS RowNUmber,ID,Name FROM@Employee) A