Common table expressions (CTEs) are a great way to temporarily store a set of results and query that result set without the need to persist it to the database. Unlike sub-queries, CTEs can be referred to more than once in the subsequent query, without the need to re-include the entire coding. There is also the opportunity of creating multiple CTEs in the same query, when for example data needs to be extracted from different tables in the database.
Scenario
To set the scenario, imagine that a retail company have daily sales for different platforms saved in one database within different transactional tables. This could be website, mobile phone application and in-store sales. In this scenario the different tables need to be combined into one.
Solution
In this scenario, a possible solution is to model a target table which has all the columns required to accommodate the data from multiple source tables into one table.
The source tables might have different names or maybe need some conversions before they can fit within the standardized columns of the target table.
Let’s create a select statement, using CTEs, to showcase the expected results, adding any filtering conditions to focus on specific records and also doing any transformations or aliases for example:
;WITH ApplicationSales AS( SELECT ST.SalesHeaderId ,ST.SaleDate AS Date ,ST.CustomerKey as CustomerId ,ST.SaleAmount ,ST.createdBy ,ST.createdOn ,’Application’ AS [Source] FROM [Application].[SalesHeaderTransaction] ST WHERE YEAR(SaleDate) = ‘2020’ ), WebsiteSales AS( SELECT AT.HeaderId AS SalesHeaderId ,AT.FiscalDate ,AT.CustomerId , CONVERT(DECIMAL(16,4), AT.SaleAmount) AS SaleAmount ,AT.InputtedBy AS createdBy ,AT.InputtedOn AS createdOn ,’Website’ AS [Source] FROM [Website].[SalesHeaderTransaction] AT WHERE YEAR(FiscalDate) = ‘2020’ ), StoreSales AS( SELECT AT.SalesHeaderId ,AT.Date ,AT.CustomerId ,AT.Amount AS [SalesAmount] ,AT.createdBy ,AT.createdOn ,’Store’ AS [Source] FROM [Store].[SalesHeaderTransaction] AT WHERE YEAR(Date) = ‘2020’ )
Then in order to combine all the different CTEs holding all the data from the distinct transactional tables, another CTE can be used to UNION the tables together (now that we have same number of columns in each). The order of the full script can be seen below:
;WITH ApplicationSales AS( SELECT ST.SalesHeaderId ,ST.SaleDate AS Date ,ST.CustomerKey as CustomerId ,ST.SaleAmount ,ST.createdBy ,ST.createdOn ,’Application’ AS [Source] FROM [Application].[SalesHeaderTransaction] ST WHERE YEAR(SaleDate) = ‘2020’ ), WebsiteSales AS( SELECT AT.HeaderId AS SalesHeaderId ,AT.FiscalDate ,AT.CustomerId , CONVERT(DECIMAL(16,4), AT.SaleAmount) AS SaleAmount ,AT.InputtedBy AS createdBy ,AT.InputtedOn AS createdOn ,’Website’ AS [Source] FROM [Website].[SalesHeaderTransaction] AT WHERE YEAR(FiscalDate) = ‘2020’ ), StoreSales AS( SELECT AT.SalesHeaderId ,AT.Date ,AT.CustomerId ,AT.Amount AS [SalesAmount] ,AT.createdBy ,AT.createdOn ,’Store’ AS [Source] FROM [Store].[SalesHeaderTransaction] AT WHERE YEAR(Date) = ‘2020’ ), CombineCTE AS( SELECT * FROM ApplicationSales UNION ALL SELECT * FROM WebsiteSales UNION ALL SELECT * FROM StoreSales ) INSERT INTO [Sales].[HoldingTable]( Date, CustomerId, SaleAmount, createdBy, createdOn, Source) SELECT Date , CustomerId , SaleAmount , createdBy , createdOn , Source FROM CombineCTE ;
Such a script can be used in a stored procedure which can then be executed in either directly in SQL or even called through services like Azure Data Factory. This is just one-use case for the scenario and there are multiple reasons why CTEs can be beneficial or utilized as an alternative to Temporary Tables or sub-queries in SQL.
Pareto Charts in Power BI and the DAX behind them
The Pareto principle, commonly referred to as the 80/20 rule, is a concept of prioritisation.
Apr
Databricks: Cluster Configuration
Databricks, a cloud-based platform for data engineering, offers several tools that can be used to
Apr
AI Assistance in Microsoft Fabric
The exponential growth of Large Language Models (LLMs) couples with Microsoft’s close partnership with OpenAI
Apr
10 reasons why it’s worth the effort to understand the value of your data
“If leaders really want to create a data driven culture, the journey starts with them!
Apr
Content Safety in Azure AI Studio
Azure AI Content Safety is a solution designed to identify harmful content, whether generated by
Apr
Model Benchmarks in Azure AI Studio
In the constantly changing field of artificial intelligence (AI) and machine learning (ML), choosing the
Apr
Celebrating International Women’s Day: from Classroom to Code
As we celebrate International Women’s Day, I want to share my journey of breaking stereotypes
Mar
Pretty Power BI – Adding Pagination to Bar Charts
Good User Experience (UX) design is crucial in enabling stakeholders to maximise the insights that
Feb