SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH percentages AS (
    SELECT 'A' AS category, 0.3 AS category_percentage UNION ALL
    SELECT 'B', 0.5 UNION ALL
    SELECT 'C', 0.2
),
data AS (
    SELECT 1 AS item_id, 'A' AS category UNION ALL
    SELECT 2, 'B' UNION ALL
    SELECT 3, 'B' UNION ALL
    SELECT 4, 'C' UNION ALL
    SELECT 5, 'A' UNION ALL
    SELECT 6, 'C' UNION ALL
    SELECT 7, 'B' UNION ALL
    SELECT 8, 'B' UNION ALL
    SELECT 9, 'B' UNION ALL
    SELECT 10, 'A' UNION ALL
    SELECT 11, 'A' UNION ALL
    SELECT 12, 'C' UNION ALL
    SELECT 13, 'C' UNION ALL
    SELECT 14, 'B' UNION ALL
    SELECT 15, 'A' UNION ALL
    SELECT 16, 'C' UNION ALL
    SELECT 17, 'A' UNION ALL
    SELECT 18, 'B'
),
sample_size AS (
    -- Define the sample size here.
    -- In this example, the sample size is set to 10.
    SELECT 10 AS sample_size
),
category_counts AS (
    -- Calculate the number of rows to select from each category based on the sample size and the percentages defined in the `percentages` CTE.
    SELECT p.category,
           CEILING(s.sample_size * p.category_percentage) AS num_rows_to_select
    FROM percentages p CROSS JOIN sample_size s
),
seqnums AS (
    -- Assign a random sequence number to each row within each category using the `ROW_NUMBER()` function.
    SELECT d.category,
           d.item_id,
           ROW_NUMBER() OVER (PARTITION BY d.category ORDER BY NEWID()) AS seqnum,
           c.num_rows_to_select
    FROM data d JOIN category_counts c ON d.category = c.category
)
SELECT d.*
FROM data d JOIN seqnums s ON d.category = s.category AND d.item_id = s.item_id
WHERE s.seqnum <= s.num_rows_to_select
ORDER BY d.category

item_id	category
17	A
15	A
11	A
3	B
18	B
2	B
8	B
14	B
16	C
4	C