Last week, we looked at how to construct a visual representation of a hierarchy stored as a parent-child table. The obvious next step is to accumulate values stored on those nodes using this hierarchy.
Category: By difficulty
An indented representation of a parent-child hierarchy
When you’re designing reports, they can often be based on hiearchies represented by “nodes” in a parent-child setup. To the end-user, the parent-child representation doesn’t provide very much readability, so you need to output this information in a human-readable form, for instance in a table where the names/titles are indented.
Working with NULL values
NULL is an undefined value and as such, it has a special status as values and datatypes go in SQL Server. There are quite a few potential pitfalls to watch out for when you’re dealing with data that can contain NULL values.
Splitting a range string into a table
This week’s post is a requirement that I see very regularly as a developer. You get a plaintext string containing one or more ranges. Each range is comma delimited, and the start and end values of the range are separated by a dash. The string could look something like this, for example: 100-120,121-499,510,520,790-999.
Wouldn’t it be practical if we could construct a table value function that returns one row for each range, with columns for the start and end of each range?
Temp table myths
Check out this deepdive on table variables that I found the other day:
SQL in the Wild: A trio of table variables
The article deals with some common misconceptions about table variables and it’s considerably more in-depth than my post on different types of temp tables.
Parsing parenthesis expressions
Today’s post illustrates a pretty cool application of SQL Server’s built-in XML and XQuery support, used to parse parenthesis-delimited expressions. You may want to get your reading glasses on for this one..
“Unpivot” using CROSS APPLY
I personally find the UNPIVOT syntax in T-SQL a bit unwieldy and not entirely easy to read. In this article, I’m going to show you an alternative way to perform UNPIVOT, using the CROSS APPLY operator.
Cross-UPDATEs
A cross-UPDATE is an UPDATE operation that includes one or more other tables, using a JOIN. This is very useful in order to update one table with a value derived from another table.
Calculating median using the NTILE function
A while back, I was shown an absolutely gorgeous median calculation, using the new OFFSET/FETCH functionality introduced in SQL Server 2012. But this got me thinking. How can you calculate the median in an older SQL Server? Here’s an idea of how to do it using the NTILE() function.
Aggregate string concatenations
A common reporting scenario is that your report has to concatenate an aggregate of string values from rows. Many other database platforms even have built-in aggregate functions that will concatenate text for you (like LISTAGG() on Oracle). In this post, we’ll take a look at how you can achieve the same results in T-SQL, using the APPLY operator and the XML datatype.