Advanced SQL querying techniques in SQL Server are essential for retrieving complex data efficiently and effectively. Here are several techniques that can be used for complex data retrieval:

1. Subqueries

Correlated Subqueries: Subqueries that reference columns from the outer query, are useful for conditions that depend on values from the main query.

Example:

SELECT CustomerID, CustomerName
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
);

Derived Tables: Subqueries used in the FROM clause to create virtual tables that can be joined or filtered.

Example:

SELECT *
FROM (
    SELECT ProductID, ProductName, UnitPrice
    FROM Products
    WHERE Discontinued = 0
) AS ActiveProducts
WHERE UnitPrice > 50;

2. Common Table Expressions (CTEs)

Recursive CTEs: Used to handle hierarchical data structures, such as organizational charts or bill of materials.

Example:

WITH RecursiveCTE AS (
    SELECT EmployeeID, FirstName, LastName, ManagerID
    FROM Employees
    WHERE EmployeeID = 1  -- Anchor member(s)
    
    UNION ALL
    
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID
    FROM Employees e
    INNER JOIN RecursiveCTE r ON e.ManagerID = r.EmployeeID
)
SELECT * FROM RecursiveCTE;

3. Window Functions

ROW_NUMBER, RANK, DENSE_RANK: Assigns a unique sequential number or ranking to each row within a partition of a result set.

Example:

SELECT
    EmployeeID,
    FirstName,
    LastName,
    DepartmentID,
    ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowNum
FROM Employees;

LAG and LEAD: Accesses data from a previous or subsequent row in the result set without using a self-join.

Example:

SELECT
    OrderID,
    OrderDate,
    Quantity,
    LAG(OrderDate) OVER (ORDER BY OrderDate) AS PreviousOrderDate,
    LEAD(OrderDate) OVER (ORDER BY OrderDate) AS NextOrderDate
FROM Orders;

4. Pivoting and Unpivoting

PIVOT: Transforms row-level data into columnar data based on an aggregate function.

Example:

SELECT *
FROM (
    SELECT ProductID, Category, Quantity
    FROM ProductSales
) AS SourceTable
PIVOT (
    SUM(Quantity)
    FOR Category IN ([Electronics], [Clothing], [Books])
) AS PivotTable;

UNPIVOT: Transforms columnar data into row-level data.

Example:

SELECT ProductID, Category, Quantity
FROM (
    SELECT ProductID, Electronics, Clothing, Books
    FROM ProductSales
) AS SourceTable
UNPIVOT (
    Quantity FOR Category IN (Electronics, Clothing, Books)
) AS UnpivotTable;

5. Dynamic SQL

Sp_executesql: Executes dynamically built SQL statements or batches.

Example:

DECLARE @sql NVARCHAR(MAX);
DECLARE @param NVARCHAR(100) = 'Electronics';

SET @sql = N'SELECT * FROM Products WHERE Category = @category';

EXEC sp_executesql @sql, N'@category NVARCHAR(100)', @category = @param;

6. Advanced Joins

Self Joins: Joining a table to itself.

Example:

SELECT e.EmployeeID, e.FirstName, e.LastName, m.ManagerID, m.FirstName AS ManagerFirstName, m.LastName AS ManagerLastName
FROM Employees e
INNER JOIN Employees m ON e.ManagerID = m.EmployeeID;

Non-Equi Joins: Joins using operators other than =.

Example:

SELECT *
FROM Orders o
JOIN OrderDetails od ON o.OrderID = od.OrderID AND o.TotalAmount > od.UnitPrice;

These advanced SQL querying techniques provide powerful tools for handling complex data retrieval tasks efficiently in SQL Server. They allow you to write sophisticated queries that manipulate data, perform calculations, and present results in ways that meet specific business requirements.