If you’ve worked with reporting, you’ve probably come across the following problem. You have a list of values, say “A, B, C, D, K, L, M, N, R, S, T, U, Z” that you want to display in a more user-friendly, condensed manner, “A-D, K-N, R-U, Z”.
Today, we’re going to look at how you can accomplish this in T-SQL, and what this has to do with window functions and gaps and islands.
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.
In this short tutorial, we’ll look at traversing parent-child structures using recursive common table expressions, and turning the data into human-readable lists. This is a great way to represent paths and hierarchy-based data in reports and end-user outputs.
This article will show you how to create a “top 10” list of data, as well as providing an aggregate on the remainder of the data. There are a few different approaches to this.