-- Works for cnt <= 10
-- Expand the inline tally for cnt > 10
;WITH Process AS (
SELECT t2.*, t1.cnt,
rn = ROW_NUMBER() OVER(PARTITION BY c2 ORDER BY c1)
FROM (SELECT c1, c2, GroupSize = COUNT(*) OVER(PARTITION BY c2) FROM #t2) t2
LEFT JOIN #t1 t1
ON t1. = t2.c2
CROSS APPLY (
SELECT TOP(CAST(ROUND(t1.cnt/(GroupSize*1.0), 0) AS INT)) n
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)
) x
)
SELECT c1, c2
FROM Process
WHERE cnt >= rn
ORDER BY c1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden