Take maximum advantage of Grouping Sets, Roll-up, Cube and Pivoting

This article explains, with detailed examples, how to use PIVOTING to take maximum advantage of queries making use of GROUPING SETS. Besides, ROLLUP and CUBE operators will be analysed as well. We'll see throughout the article that it's very easy to get simple, practical and powerful queries by integrating all of them in the same query.

I think that master these operators contributes to make cleaner code and save a lot of time when developing. Thus, I hope this article will be useful to both improve your knowledge and develop faster cool queries for your reports, forms or views.

[More]

Row Level Security in SQL Server

Row-Level Security let us control access to data at row level for any SQL Server table based on users, roles, membership or working context. It tremendously simplifies and improve the options we have to secure, filter, implement restrictions or eventually deal with our data over certain scenarios. What is most relevant for me is that all the security policy can be set centrally at a database level preventing developers from taking care of adding custom where clauses to enforce security. It makes the management of data much more reliable and maintainable.

[More]

Simple way of converting elements in a comma delimited string into row values in a SQL table

In my previous article Simple way to get comma delimited values from a field by using SQL I showed how to get a comma delimited string from a field in a SQL table. But, there are other times you want to get the same but the other way around, that is, converting each element in a comma (or another character) delimited string variable into a row value in a table. How to get this, then?

[More]

Using PIVOT with SQL Server

PIVOT operator is mainly used to convert a result set from a SQL query into another one by rotating unique values in a specified field column, throughout the original result set, into new column fields in the final result set

[More]

SQL Training with Recursive Queries and Windowing Functions

In this article I'm going to show some useful SQL using "Recursive Queries" and "Windowing Functions". So, the article is meant to intermediate level SQL programmers or those who want to know a little more about this language. Detailed explanations about syntax can be obtained in Microsoft documentation (Windowing | Recursivity). It's not the goal of this article to explain all that again. So, have a look there to go deeper into it.

[More]

Leverage Unpivoting with SQL at a glance

Some days ago, I had to help others to solve a problem with SQL. This issue was related to convert fields in a result set into values in rows into the final desired result set. So, we are talking about making use of UNPIVOTING functionality. Finally, I thought, why not writing a little tip about?

[More]

Recursive SQL with Common Table Expressions

This post intends to set out how to make recursive SQL queries in a simple and straightforward manner using Common Table Expressions (CTE).

What are CTEs?

As it is said in Microsoft SQL Server documentation a CTE (Common Table Expression) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing (important for the purposes in this post!) and can be referenced multiple times in the same query.

[More]