# The Tally Table and Expanding Rows

Steve Jones, 2012-05-22

Suppose you had some data like this:

Class           Limit
————— ———–
Calculus        5
History         4
Physics         2

But what you really want is this:

Class           Student
————— ———–
Calculus
Calculus
Calculus
Calculus
Calculus
History
History
History
History
Physics

Physics

Where you have a placeholder for each student. This is a little contrived, but for the sake of the scenario, how do you actually expand the data in the first set to the second?

An easy way is a cursor, but suppose you had a large school environment and you were doing this regularly, you might not want the performance hit of a cursor. Suppose you have some similar scenario, like slotting inventory or holding places for some large report? You would want a better solution.

## Setup

Let’s first get a table ready for this.

```declare @Table table (
Class varchar(15)
, Limit int
)
;
insert into @Table
values ('Calculas',5)
, ('History', 6)
, ('Physics', 2)
;
```

## A Tally Table

I first saw this when Jeff Moden talked about this in his “The Numbers or Tally Table” article. It seemed like a neartidea, and he improved upon is in his next piece, finding a more efficient way to generate the data. An even more flexible idea that I like came from his Test Data Generator article, where he uses sys.columns to build a list as large as he would like. I found it to be a great idea, and this problem is a great way to apply it.

Here’s a short look at the code that makes this work.

```SELECT TOP (4) RowNum = ROW_NUMBER() OVER(ORDER BY a.[Name])
FROM sys.columns a, sys.columns b```

In this code, a cross join occurs between sys.columns and itself. With the addition of the ROW_NUMBER() function, and an order by, you can easily get a list of numbers. If I run this. I get:

RowNum

——————–

1

2

3

4

If I change my TOP value, I can get different rows:

```SELECT TOP (6) RowNum = ROW_NUMBER() OVER(ORDER BY a.[Name])
FROM sys.columns a, sys.columns b```

RowNum

——————–

1

2

3

4

5

6

To make this work for me in the original problem, I will use the limit value for my TOP in a subquery. However I can’t do a straight join, I need a CROSS APPLY, that will execute the right side of the CROSS apply for each input row from the left table.. It’s intended to be for table valued functions, but in this case, we’ll just return a table from the right side.

The code looks like this:

```SELECT
c.Class
, 'Student' = ' '
FROM (
SELECT Class,Limit
FROM @Table
) c
CROSS APPLY (
SELECT TOP (Limit) RowNum = ROW_NUMBER() OVER(ORDER BY a.[name])
FROM sys.columns a, sys.columns b
) n```

What happens is that for each row, the derived table on the right side of the CROSS APPLY, returns a set of numbers which are then linked to the row on the left side of the CROSS APPLY.

For the first row in @table, we have “Calculus” and “5”. When we apply this row to the subquery on the right side (SELECT TOP()…), we get these results:

1

2

3

4

5

When this is joined with the row and the c.Class returned, we end up with the same value being returned 5 times. That results in

Calculus

Calculus

Calculus

Calculus

Calculus

If this is repeated for each of the other rows, we get 4 and 2 rows from the right side of the CROSS APPLY returned, respectively.

The final results are what we get at the top of this post.

Filed under: Blog Tagged: syndicated, T-SQL

# Book Review: Big Red – Voyage of a Trident Submarine

I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine…

Andy Warren

2009-03-10

# Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

# Inserting Markup into a String with SQL

In which Phil illustrates an old trick using STUFF to intert a number of substrings from a table into a string, and explains why the technique might speed up your code…

Phil Factor

2009-02-18

# Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17