Duplicating rows based on a count column SQL Server [duplicate]

Question

I have the following table:

MyTable
PlaceID NameID  Code    CodeCount
1       1       1234A   2
1       2       1234A   3
1       3       1234A   1
2...
2...
3...
3...

I want to select 1234A where PlaceID = 1, the number of times equal to the sum of the CodeCount. So, the result would be six rows like this:

MyResult
Code    
1234A
1234A
1234A
1234A
1234A
1234A

Eventually, this selection will have a bunch of different codes for a single PlaceID. YES I know I'm duplicating data, in the end it's not going to be a lot and I'll explain why I'm doing this below.

First, I needed to get the sum of the counts:

DECLARE @cdcnt INT = (SELECT SUM(CodeCount) FROM MyTable WHERE PlaceID = 1 AND Code = '1234A')

Then I can use this value as a control in a while loop to select the value that many times:

DECLARE @i INT = 0;
WHILE @i < @cdcnt
BEGIN
    SELECT DISTINCT Code
    FROM MyTable
    WHERE PlaceID = 1 AND Code = '1234A'
    SET @i = @i + 1
END

The problem is I get 6 separate selections, instead of one selection with 6 rows. How do I create MyResult shown above, instead of 6 separate selections?

Why I am doing this: I want to use a random number generator to pick a Code with the frequency at which it appears in aggregate for each PlaceID (there will be other Codes added, I'm still figuring out how to do just one). I am open to suggestions for different ways of doing this, but it's the only way I could figure out how. If there's a function already built to pick from a list according to a set frequencies, I'm all ears.

Answer 1

Below is an example using a join to a tally CTE. It would be better to create a materialized tally/numbers table to facilitate this sort of task going forward and improve performance too.

WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,codes AS (SELECT Code, SUM(CodeCount) AS CodeCount FROM dbo.MyTable WHERE Code = '1234A' AND PlaceID = 1 GROUP BY Code)
SELECT Code
FROM codes
JOIN tally ON tally.num <= codes.CodeCount;