Exploding Rows With Tally Tables

Riley Major shows how to use a tally table to turn one row into several rows:

Imagine a simple table containing contact information. It has two places for phone numbers (Phone1 and Phone2). Let’s say you wanted to split those off into a related phone numbers table so you could support many more numbers for a single contact. As part of that process, you’d need to create two rows for each existing contact– one for each phone number.

One way to get two phone numbers is to handle each phone number as a separate process, combining a list of all contacts and their first number with those same contacts and their second number. So you’d SELECT everything from the table once for the first phone number and then UNION it with another SELECT. This works, but if you have a large list of columns or a complex set of JOINs, you end up repeating a lot of code.

Instead, what if you simply, magically double the rows and then pick and choose the columns you wanted from each of the rows?

Tally tables are a great “Get out of a bad design (relatively) cheap” tool.  They’re not something I use on a daily basis, but they’ve made life easier for me on dozens of occasions.

Related Posts

Updating Tables With Faked Data

Phil Factor continues his data obfuscation series: We are taking a slow-but-steady approach. We rewrite our code from the previous blog post that assembles the string; it now uses a view to get its random numbers, and we’ll speed it up slightly by putting a bit more intelligence into the markov table. We then put […]

Read More

COUNT And NULL Values

Kenneth Fisher digs into the COUNT() function and sees how it deals with NULL values: Count the number of values 1 2 3 SELECT COUNT(FieldName) FROM TableName; -- or SELECT COUNT(ALL FieldName) FROM TableName; The ALL argument is the default and is unnecessary (I didn’t even know it existed until I started this post). Here you are counting […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

July 2018
MTWTFSS
« Jun  
 1
2345678
9101112131415
16171819202122
23242526272829
3031