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