Section 7.7. Folding Tables

   

7.7 Folding Tables

7.7.1 Problem

You imported a wide, non-normalized table with columns organized in an array-like manner. You need to convert the data in that table to a more normalized form. For each non-normalized row in the original table, you need to generate several rows of normalized output. Taking the ranking spreadsheet from the student's office, you would prefer to have a list-like report, where the rank for each student in a year would represent one row, so the columns named Y2000, Y2001, and Y2002 are the ones that need to be flattened. Given the data shown earlier in this chapter for the ranking spreadsheet, you want to see output like this:

 Name       Year Rank         ---------- ---- -----------  Anna       2000 1 Anna       2001 2 Anna       2002 3 Billy      2000 4 Billy      2001 5 Billy      2002 6 Joe        2000 7 Joe        2001 8 Joe        2002 9 ... 

7.7.2 Solution

The act of taking a row in table and converting it into two or more narrower rows in an output table is referred to as folding . There are at least three approaches that you can take to fold data. An easy-to-understand approach is to use a union query. In some cases, you may be able to fold the data by joining the table to itself. A more general approach is to fold the data using a Pivot table.

7.7.2.1 Using a union query to fold data

A very easy-to-understand approach to folding data is to write one SELECT statement for each column that you want to fold and then union all those statements together. With respect to the bookstore example being used for this recipe, you could use the following query to fold the three columns:

 SELECT Name, '2000' Year, Y2000 Rank FROM StudentRankings UNION SELECT Name, '2001', Y2001 FROM StudentRankings UNION SELECT Name, '2002', Y2002 FROM StudentRankings 

The first SELECT statement returns the data for the year 2000, the second returns data for the year 2001, and the third returns data for the year 2002. The column alias Rank is used in the first SELECT statement to provide a name for the column containing the ranking data.

This appears to be a simple and straightforward solution, so why worry about any other approach? There are two reasons, actually, why you might not want to use a UNION query like the one shown here. One reason is that older versions of SQL servers didn't allow a UNION query to be encapsulated into a view. The other reason is performance. In the example shown here, three SELECT statements are being executed, requiring the server to make three passes through the table. For a small table, that might not present a problem, but as the amount of data in the table increases , so will the performance hit that you take by doing this.

7.7.2.2 Using a self-join to fold data

You can use a self-join to fold the data if the following statements are true:

  • You have at least as many rows in the table as you have columns to fold

  • You have a numerical column, such as an Id column

  • The values in the numerical column begin with 1 and are guaranteed to be sequential

We are folding three columns (Y2000, Y2001, and Y2002), and our StudentRankings table has three rows, so the first point is covered. The row Id number is sequential, starts with 1, and has no gaps, so the second two points are also covered.

The following query, then, can be used to fold the three volume columns into one:

 SELECT t1.Name Name,    (CASE WHEN t2.Id=1 THEN '2000'        WHEN t2.Id=2 THEN '2001'        WHEN t2.Id=3 THEN '2002' END) Year,    (CASE WHEN t2.Id=1 THEN t1.Y2000        WHEN t2.Id=2 THEN t1.Y2001        WHEN t2.Id=3 THEN t1.Y2002 END) Volume FROM StudentRankings t1, StudentRankings t2 WHERE t2.Id BETWEEN 1 AND 3  ORDER BY Name, Year 

If you've never seen a query like this before, you may need to stare at it for a while before it begins to make sense. All the data comes back from the occurrence of the StudentRankings table that has the alias name t1. The WHERE clause restricts the t2 table so that only three rows are returned. These three rows have Id numbers 1, 2, and 3 and are used to control the results returned by the CASE statements. The discussion section for this recipe explains how this works in more detail.

7.7.2.3 Using the Pivot table to fold data

If you can't do a self-join with your data, you can use a more general approach to folding data that involves a Pivot table.

You need to have at least as many rows in the Pivot table as columns that you are folding. Just be sure that your Pivot table is large enough. Once the Pivot table has been created and populated , you can use it to fold the data by joining it to your data table. The following query, for example, folds the data in the StudentRankings table:

 SELECT Name Name,    (CASE WHEN i=1 THEN '2000'        WHEN i=2 THEN '2001'        WHEN i=3 THEN '2002' END) Year,    (CASE WHEN i=1 THEN Y2000        WHEN i=2 THEN Y2001        WHEN i=3 THEN Y2002 END) Volume FROM StudentRankings, Pivot WHERE i BETWEEN 1 AND 3  ORDER BY Name, Year 

This query is essentially the same as the one shown earlier in the section on self-joins. The only difference is that the t2 table, in this case, is the Pivot table and not another instance of the StudentRankings table.

7.7.3 Discussion

The table-folding techniques shown here can be quite useful when working with data from legacy systems. These systems often have tables or other data structures containing repeating data elements, such as the three rank columns in our example. By folding the tables containing data from such systems, you can restructure the data into a more normalized form. The normalized data is then more easily used in relational operations, such as a table-join or a group -by summarization.

Typically, table folding is done in batch procedures when data from an external system is imported into a SQL database. Folding is fairly efficient, since it can be done in one query. To implement the same process in a classical programming language, at least two nested loops would be needed. The first loop would need to step through the entire table, while the second would need to iterate through the columns in each row and generate a flattened result.

The self-join shown in this recipe is very similar to the join used with the Pivot table. With the self-join solution, the second occurrence of the table was limited to only the first three rows. It effectively functioned as the Pivot table. Why, then, do we show both solutions? The self-join is shown here partly for artistic reasons. Some programmers simply do not like to have more tables than absolutely necessary. It's also easier to write one query than it is to create a Pivot table, populate it with data, and then write a query. From a performance point of view, however, the Pivot table query is better. If you are writing a batch procedure that is going to be used often, the Pivot table solution is the one to go with.

The logic behind the two join queries is quite simple. They both use two tables. The information is taken from the first table. The second table helps in folding the first. In general, if you join a table with N rows and a table with M rows, the result has N * M rows. Each row from the first table is matched with each row from the second one. This behavior is exactly what we need to fold data. The second table is used as a guiding table that extracts M (in our case, three) columns from the first one.

The following data represents the intermediate result set produced by the join operation in the queries:

 Name        Y2000     Y2001     Y2002     i ----------- --------- --------- --------- ----------- 1           1         2         3         1 1           1         2         3         2 1           1         2         3         3 2           4         5         6         1 2           4         5         6         2 2           4         5         6         3 3           7         8         9         1 3           7         8         9         2 3           7         8         9         3 

As you can see, each data row is repeated three times. This gives us one row for each of the three columns that we need to fold. The i column (which was t2.id in the self-join) distinguishes between the three, otherwise duplicate, rows. The i column can then be used within a CASE statement to return the rank column of a student for each year.

There are actually two CASE statements in the query, and they are almost the same. The first CASE statement uses the i value as a basis for returning a year. The second uses the i value as a basis for returning the student's rank value for that year.

   


Transact-SQL Cookbook
Transact-SQL Cookbook
ISBN: 1565927567
EAN: 2147483647
Year: 2005
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net