Data Transformation
Explore how to transform data using subqueries within SELECT and WHERE clauses, understand the use of CAST and CONVERT for data type conversions, and combine result sets with UNION and UNION ALL. This lesson helps you gain practical skills in handling complex data transformation techniques in SQL Server.
We'll cover the following...
The examples in the lessons refer to the “Sales.ProductSales” table.
Sales.ProductSales Table
TransactionID | ProductID | UnitPrice | Quantity | CustomerID | Supt_PkgID | Supt_PkgCost |
1 | 1 | 1000.00 | 1 | 1 | 1 | 0.00 |
2 | 1 | 1000.00 | 1 | 2 | 1 | 0.00 |
3 | 2 | 1500.00 | 2 | 3 | 2 | 250.00 |
4 | 3 | 750.00 | 1 | 4 | 2 | 250.00 |
5 | 4 | 2500.00 | 1 | 5 | 2 | 250.00 |
6 | 1 | 1000.00 | 1 | 5 | 2 | 250.00 |
Subqueries
A subquery is a query that is nested within another query. These can be nested in SELECT, INSERT, UPDATE, and DELETE statements, as well as the WHERE clause. When used in the WHERE clause, it creates further conditions.
In the example below, the inner query in line 3 runs first and returns the highest value in the UnitPrice column. The outer query will then run and use the results from the subquery for its WHERE condition. Overall, we want to obtain all columns/headers for the ...