---
title: "Top 10 SQL Queries for Efficient Data Transformation"
description: "Discover essential SQL queries for transforming raw data into actionable insights. Ideal for ETL processes and real-world projects."
url: https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/
date: 2023-12-24
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2023/12/sql-queries-for-data-transformation.jpg?fit=2240%2C1260&ssl=1
categories: ["SQL"]
tags: ["SQL best practices", "SQL for Data Analysts", "SQL Queries", "Transform Data with SQL"]
type: post
lang: en
---

# Top 10 SQL Queries for Efficient Data Transformation

***In today’s data-driven world, turning raw data into helpful insights is crucial—and SQL is the key tool for this.** Whether you’re a data engineer creating ETL pipelines or an analyst making reports, knowing [how to](https://srinimf.com/2024/10/01/how-to-build-sql-query-step-by-step-guide/) work with data using SQL is essential. In this post, we’ll cover **10 essential [SQL queries](https://srinimf.com/2014/01/20/238-top-sql-queries/)** commonly used in real-world data transformation. These examples will help you improve operations, maintain data quality, and maximize your datasets.*

Table of contents

1. [Filtering Rows](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/#filtering-rows)
2. [Sorting Data](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/#sorting-data)
3. [Aggregation](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/#aggregation)
4. [Joining Tables](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/#joining-tables)
5. [Subqueries](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/#subqueries)
6. [CASE Statements](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/#case-statements)
7. [UNION and UNION ALL](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/#union-and-union-all)
8. [Window Functions](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/#window-functions)
9. [Pivoting and Unpivoting](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/#pivoting-and-unpivoting)
10. [Common Table Expressions (CTE)](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/#common-table-expressions-cte)
11. [Conclusion](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/#conclusion)
12. [References](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/#references)

## **Filtering Rows**

Filters rows that meet specific criteria. It uses the [WHERE condition](https://srinimf.com/2023/11/09/how-to-update-a-table-neat-way-using-subquery/)to filter rows based on a specified condition. It shows in the result set Only rows that satisfy the condition.

```
SELECT * FROM TABLE1 WHERE Condition;
```

## **Sorting Data**

The ORDER BY clause sorts the result set on one or more columns in [ascending (default) or descending order](https://srinimf.com/2018/11/04/the-order-of-sql-query-execution-read-today/) based on one or more columns.

```
SELECT * FROM TABLE1 ORDER BY Column1, Column2;
```

## **Aggregation**

**Summarizing data using aggregate functions.** Aggregate function `COUNT` and `AVG` are used to summarize data. The `GROUP BY` clause groups the result set by one or more columns.

```
SELECT Column, COUNT(*), AVG(Value) 
FROM TABLE1 
GROUP BY Column;
```

## **Joining Tables**

**Combining data from multiple tables. [The `INNER JOIN` combines](https://srinimf.com/2023/10/21/12-top-sql-joins-interview-questions/) rows from two tables based on a related column (in this case, where `Table1.ID` is equal to `Table2.ID`). **

```
SELECT * FROM Table1 
INNER JOIN Table2 
ON Table1.ID = Table2.ID;
```

## **Subqueries**

**Using a query within another query.** A subquery is a query nested within another query. The outer query selects rows where a column matches values returned by the inner query.

```
SELECT * FROM TABELE1 
WHERE Column IN (SELECT Column FROM TABLE2 WHERE Condition);
```

## **CASE Statements**

**Conditional transformations within a query.** `CASE` statements perform conditional transformations within a query. Depending on specified conditions, different values are returned for a new column

```
SELECT Column, 
CASE WHEN Condition1 THEN 'Result1' 
WHEN Condition2 THEN 'Result2' 
ELSE 'DefaultResult' 
END AS TransformedColumn FROM TABLE1;
```

## **UNION and UNION ALL**

**Combining the results of two or more SELECT statements.** [`UNION` and `UNION ALL`](https://srinimf.com/2017/10/21/sql-query-with-case-logic-avoid-union-why/) combining the results of two or more `SELECT` statements. `UNION` removes duplicate rows, while `UNION ALL` including all rows.

```
SELECT Column FROM Table1 UNION SELECT Column FROM Table2;
```

## **Window Functions**

**Performing calculations across a specified range of rows.** Window functions perform calculations across a specified range of rows defined by the `OVER` clause. Examples include calculating running totals or averages.

```
SELECT Column, SUM(Value) 
OVER (PARTITION BY PartitionColumn ORDER BY OrderColumn) 
AS RunningTotal FROM TABLE1;
```

## **Pivoting and Unpivoting**

Transforming rows into columns (pivoting) or vice versa (unpivoting). Pivoting transforms rows into columns, while unpivoting transforms columns into rows. Below, you’ll find examples of PIVOT and UNPIVOT clauses.

```
-- Create a new table to store the PIVOT results
CREATE TABLE pivot_table AS 
SELECT *
FROM
  (SELECT ord_num, ord_amount, agent_code
   FROM orders)
PIVOT
  (SUM(ord_amount)
  FOR agent_code IN ('A010' AS A010, 'A011' AS A011, 'A004' AS A004));

-- Create a new table to store the UNPIVOT results
CREATE TABLE unpivot_table AS 
SELECT ord_num, ord_amount, agent_code
FROM
  (SELECT ord_num, A010, A011, A004
   FROM pivot_table)
UNPIVOT
  (ord_amount FOR agent_code IN (A010, A011, A004));

-- Optional: Display the contents of the new tables
SELECT * FROM pivot_table;
SELECT * FROM unpivot_table;
```

## **Common Table Expressions (CTE)**

These simplify complex queries by defining temporary result sets. CTEs provide a way to create a temporary result set. Later, you can use it in the SELECT, INSERT, UPDATE, or DELETE statement.

```
WITH CTE AS ( SELECT Column FROM TABLE1 WHERE Condition ) 
SELECT * FROM CTE;
```

## **Conclusion**

These queries cover the common or frequent data transformation tasks. Depending on your specific use case, you may need to adapt and combine these techniques for more complex transformations.

## **References**

- [Table Data for creating Tables and to practice SQL queries](https://www.w3resource.com/sql/sql-table.php)

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2023%2F12%2F24%2F10-common-sql-queries-that-use-in-data-transformation%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2023%2F12%2Fsql-queries-for-data-transformation.jpg%3Ffit%3D1200%252C675%26ssl%3D1&description=Top%2010%20SQL%20Queries%20for%20Efficient%20Data%20Transformation)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2023/12/24/10-common-sql-queries-that-use-in-data-transformation/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Top%2010%20SQL%20Queries%20for%20Efficient%20Data%20Transformation&body=https%3A%2F%2Fsrinimf.com%2F2023%2F12%2F24%2F10-common-sql-queries-that-use-in-data-transformation%2F&share=email)
-

### Like this:

Like Loading…