Multiplying rows by a factor in a column

Posted: October 27, 2015 in CTE, Cursors, TSQL

The Problem

I had a table which represented clusters of an incident. I then wanted 1 row for every instance of that incidence. So,

DayName InstanceCount
Day 1 2
Day2 3
Day3 1

Would become:

DayName
Day1
Day1
Day2
Day2
Day2
Day3

The Solution

At first I used a cursor and entered each row into a new table.
I wasn’t happy with the cursor so looked into other answers and came up with this:

DECLARE @ii as int;
DECLARE @i as int = 0;
SELECT @ii = max(InstanceCount) + 10
FROM [dbo].[SourceTable];

DECLARE @NumRows as Table (num int not null);
WHILE @i < @ii
BEGIN
INSERT INTO @NumRows(num)
VALUES (@i);
SET @i = @i + 1;
END;

SELECT J.DayName
FROM [dbo].[SourceTable] as J
JOIN @NumRows as n
ON n.num < CONVERT(int, LEFT(CAST(InstanceCount AS INT ),20));

First get the highest instance count and add a few for luck. Then join this numbers table to your base table. This got the results I wanted. (I still had to use a cursor because I needed to randomise an element in each row, but that’s another story).

I watched a video by Steve Stedman (http://stevestedman.com/) and he gave a demo of a numbers table using a CTE. So I changed my code to this:

DECLARE @ii as int;
SELECT @ii = max(InstanceCount) + 10
FROM [dbo].[SourceTable];

;WITH numbers(num) as
(
SELECT 1
UNION ALL
SELECT 1 + num
FROM numbers
WHERE num < @ii
)
SELECT J.DayName
FROM [dbo].[SourceTable] as J
JOIN numbers as n
ON n.num < CONVERT(int, LEFT(CAST(InstanceCount AS INT ),20));
OPTION (MAXRECURSION 0);

Annoyingly, MAXRECURSION wouldn’t accept a variable as a value else I would have used @ii

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s