The last step in the solution is writing a query that produces the desired normalized form of the measurement samples. You need to join the TempSamples table to the Nums table, but the JOIN condition is tricky. It should include the following expression:
ON n <= <num of elements in list>
but how do you find the number of elements? The fn_getelement() function demonstrated a trick that uses the LEN() and REPLACE() functions to count the number of elements. You can use this trick in writing your final query to get the number of elements. Listing 4 shows the complete query, with an ORDER BY clause added for better formatting. All you have to do is use Listing 4's query (without the ORDER BY clause) in an INSERT INTO statement to populate the NormTempSamples table with the normalized data, and you're done.
Solution That Doesn't Use a UDF
If you're working with SQL Server 7.0 or earlier, you can't use UDFs. You can try using an auxiliary table like the one I used in the previous solution, but without a function that can extract the element that's in the desired position, you can't do much with 30 numbers representing the possible element ordinal positions. However, if you insert more numbers into the Nums table, you can use Nums to locate the commas in the list of measurements.
First, run the code that Web Listing 2 shows to insert the numbers up to 8000. I chose that number because with 8000, the query will match each number with a character in the list and because the temperatures column is defined as varchar(8000). Now you can join TempSamples to Nums based on the following JOIN condition:
ON n < LEN(temperatures) AND
SUBSTRING(',' + temperatures, n, 1) = ','
This condition returns true when the query finds a comma in the nth character. The string containing the temperatures is prefixed with a comma so that the JOIN condition will include the starting position of the first element. A JOIN query containing the above condition produces the desired number of rows per site, with each row containing the site ID and a list of measurements and with n representing the position of the first character of an element. Now all you have to do is write two expressions in the SELECT list that extract the element and calculate the element's index, as I demonstrated earlier in this article. Listing 5 shows the final query.
T-SQL gives you a powerful means of working with denormalized data. You can also use the tricks I showed in this article to migrate your data from a denormalized to a normalized form. And whenever you can, try to educate your customers about the benefits of using normalized data in their OLTP environments.
End of Article
Prev. page
1
[2]
next page -->
