Archive for October, 2015

Using the Apply operator

Posted: October 28, 2015 in Apply, SQL Bits, SQL Video, TSQL

I started this blog to remind myself how I solved certain problems or to dump code snippets. I haven’t been using it much but am going to get it going again. From now on I’m also going to include links to interesting SQL resources (mainly videos) I watch.

To start this off, I watched this one today: Boost your T-SQL with the Apply Operator. Certainly worth a watch if you are doing any T-SQL coding. It’s presented by Itzik Ben-Gan who also wrote the book T-SQL Querying, which I happen to be reading at the moment.

Advertisements

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