How to use a CTE? Should you use this “With” your SQL code?
Introduction You can use Common Table Expressions (CTEs) as temporary named result sets within a SELECT, INSERT, UPDATE, or DELETE statement. A WITH clause defines and allows the use of them to simplify complex queries and improve their readability. A CTE is created by first defining a query that generates a result set, and then […]
When to transform data
As a general principle we should always look to transform data close to its source to gain efficiencies and minimise potential for discrepancies Over the last few weeks we have shown how data can be combined from multiple sources in three different ways UNION in SQL APPEND data in Power Query UNION in DAX A […]
UNION in SQL
A Guide to Using UNION in SQL: Combining and Manipulating Data Introduction: Understanding the Purpose and Functionality of UNION in SQL The SQL UNION operator allows us to merge the results of two or more SELECT statements into a single result set. It performs a set operation by combining rows from different tables based on […]
Joins in SQL
Joins in SQL: A simplified guide Introduction to joins in SQL This guide is aimed at explaining in simple terms the different types of joins between tables in SQL We use simplified real life examples combining data from Products, Stores, Sales and Inventory What we will not do unlike other guides we have seen is […]
Automatically reformat SQL
Automatically format SQL with this great free tool At Select Distinct we love to share tips and timesavers to help you become more efficient, and when we find great tools we love to share this knowledge (We have no affiliation to this product or site and get no commercial benefit from it) PoorSQL.com is a […]
Group by ROLLUP in SQL Server
Introduction to ROLLUP in SQL Server Did you know that there is a simple way to add roll up sub totals in SQL Server? SQL ROLLUP is an extension of the GROUP BY clause in SQL, providing a way to generate subtotals and grand totals for multiple dimensions within a dataset. It enables analysts to […]
Debugging Joins in SQL
Debugging Joins in SQL Joining tables is a powerful feature in SQL that allows you to combine data from multiple tables into a single result set. However, it can sometimes be challenging to get the desired output when working with joins. In this blog post, we will discuss some common issues that arise when debugging […]
SQL Coalesce Function
An Introduction to the SQL COALESCE function The SQL COALESCE function is a simple, efficient function to handle NULL values in your SQL queries This quick guide shows you how it works, how it compares to using some alternative approaches and common use cases The SQL COALESCE function returns the first non-null value in a […]
UNPIVOT Function in SQL
How to Unpivot a Table in SQL Server The Power of the Unpivot function in SQL : How to Convert Columns into Rows in SQL Server with One Simple Operator Have you ever encountered a table that has too many columns and not enough rows? A table that looks like this: This […]
PIVOT function in SQL
Introduction to the PIVOT function in SQL The PIVOT function in SQL can be used to create a dataset with aggregated rows from a list of data This post shows you how to transform a table of data like this Into a summary table of data with a column representing each month […]
