Pivot Tables with UDFs

{

I taught a T-SQL Programming class this week and in the process looked over some old books on the subject. One in particular I've enjoyed was the Transact-SQL Cookbook from O'Reilly - I have yet to find as novel an approach to SQL, coming from the ideas of set theory rather than tutorials on querying. I'm biased too, my best friend in highschool was Slovenian and one of the authors, Aleš Špetič, hails from that fine country.

A cool idea from the very first chapter is the pivot table, a numeric range that can come in handy for many different types of operations. The book, which is probably circa Microsoft SQL Server 7, demonstrates the building of a pivot table using some hardcoded insert statements followed with a cartesian join that generates the range.

It occured to me in SQL 2000 and higher one can use a User Defined Function and get all the benefits with a little bit more flexibility. Here is a simple approach to the same concept:

CREATE FUNCTION fnPivot(@BOUND INT)
RETURNS @Pivot TABLE(I INT)
AS
BEGIN
DECLARE @I INT
SET @I = 1
WHILE @I <= @BOUND BEGIN
INSERT INTO @Pivot VALUES(@I)
SET @I = @I + 1
END
RETURN
END
GO

The approach is different but the benefits are similar. A simple one from the first chapter is building a calendar of a given range of days. I've adapted it to use the function above:

SELECT
CONVERT(CHAR(10), DATEADD(d, i, CURRENT_TIMESTAMP), 121) [date],
DATENAME(dw, DATEADD(d, i, CURRENT_TIMESTAMP)) [day]
FROM
dbo.fnPivot(7)

date day
---------- ------------------------------
2007-09-13 Thursday
2007-09-14 Friday
2007-09-15 Saturday
2007-09-16 Sunday
2007-09-17 Monday
2007-09-18 Tuesday
2007-09-19 Wednesday

}