Merge Statament

 Merge statement  in SQL Server is used to perform insert, update, or delete operations on a target table based on the results of a join with a source table. It's commonly used for synchronizing two tables. Here's a simple example:

Let's say we have two tables: TargetTable and SourceTable, both with columns ID and Value.

sql

-- Create sample tables
CREATE TABLE TargetTable (
    ID INT PRIMARY KEY,
    Value VARCHAR(50)
);

CREATE TABLE SourceTable (
    ID INT PRIMARY KEY,
    Value VARCHAR(50)
);

-- Insert sample data into TargetTable
INSERT INTO TargetTable (ID, Value)
VALUES
    (1, 'Value 1'),
    (2, 'Value 2'),
    (3, 'Value 3');

-- Insert sample data into SourceTable
INSERT INTO SourceTable (ID, Value)
VALUES
    (2, 'Updated Value 2'),
    (3, 'Value 3'),
    (4, 'Value 4');

Now, let's use MERGE to synchronize TargetTable with SourceTable:

sql

-- Merge operation
MERGE INTO TargetTable AS target
USING SourceTable AS source
ON (target.ID = source.ID)

-- When records are matched, update the values
WHEN MATCHED THEN
    UPDATE SET target.Value = source.Value

-- When there is no match, insert the records
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Value) VALUES (source.ID, source.Value)

-- When there is no match in the source, delete the records in the target
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- Display the updated TargetTable
SELECT * FROM TargetTable;

After executing the MERGE statement, the TargetTable will be synchronized with the SourceTable. In this example, the row with ID 2 will be updated with the new value from SourceTable, the row with ID 4 will be inserted into TargetTable, and the row with ID 1 will be deleted from TargetTable because it doesn't exist in SourceTable.

Pivot and Unpivot using SQL and Dynamic SQL

SQL Server, the PIVOT and UNPIVOT operators are used to transform data from rows into columns (PIVOT) or from columns into rows (UNPIVOT).

Here's an example of how you can use PIVOT:

Let's say you have a table Sales with the following columns: Product, Month, and Revenue. You want to pivot the data to show total revenue for each product across different months.

-- Sample data
CREATE TABLE Sales (
    Product VARCHAR(50),
    Month VARCHAR(50),
    Revenue DECIMAL(10, 2)
);

INSERT INTO Sales (Product, Month, Revenue)
VALUES
    ('Product_A', 'January', 1000),
    ('Product_A', 'February', 1500),
    ('Product_A', 'March', 2000),
    ('Product_B', 'January', 1200),
    ('Product_B', 'February', 1600),
    ('Product_B', 'March', 1800);

-- Pivot query
SELECT *
FROM (
    SELECT Product, Month, Revenue
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Revenue)
    FOR Month IN ([January], [February], [March])
) AS PivotTable;

This query will pivot the data to show total revenue for each product across different months.

And here's an example of how you can use UNPIVOT:

Let's say you have a table Sales_Pivot with the following columns: Product, January, February, March, and you want to unpivot this data to show the revenue for each product in each month.

sql

-- Sample data
CREATE TABLE Sales_Pivot (
    Product VARCHAR(50),
    January DECIMAL(10, 2),
    February DECIMAL(10, 2),
    March DECIMAL(10, 2)
);

INSERT INTO Sales_Pivot (Product, January, February, March)
VALUES
    ('Product_A', 1000, 1500, 2000),
    ('Product_B', 1200, 1600, 1800);

-- Unpivot query
SELECT Product, Month, Revenue
FROM (
    SELECT Product, January, February, March
    FROM Sales_Pivot
) AS SourceTable
UNPIVOT (
    Revenue FOR Month IN ([January], [February], [March])
) AS UnpivotTable;

This query will unpivot the data to show the revenue for each product in each month

 Dynamic SQL example

Dynamic SQL in SQL Server allows you to construct SQL statements at runtime, which can be useful for cases where the structure of your pivot or unpivot operation may change dynamically. Here's an example of using dynamic SQL with PIVOT and UNPIVOT:

First, let's start with a dynamic pivot example:

sql

-- Sample data
CREATE TABLE Sales (
    Product VARCHAR(50),
    Month VARCHAR(50),
    Revenue DECIMAL(10, 2)
);

INSERT INTO Sales (Product, Month, Revenue)
VALUES
    ('Product_A', 'January', 1000),
    ('Product_A', 'February', 1500),
    ('Product_A', 'March', 2000),
    ('Product_B', 'January', 1200),
    ('Product_B', 'February', 1600),
    ('Product_B', 'March', 1800);

-- Dynamic pivot query
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(Month) 
                    FROM Sales
                    FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT Product, ' + @cols + ' 
            FROM 
            (
                SELECT Product, Month, Revenue
                FROM Sales
            ) x
            PIVOT 
            (
                SUM(Revenue)
                FOR Month IN (' + @cols + ')
            ) p '

EXEC sp_executesql @query;

This dynamic pivot query will pivot the data to show total revenue for each product across different months.

Now, let's see an example of dynamic unpivot:

sql

-- Sample data
CREATE TABLE Sales_Pivot (
    Product VARCHAR(50),
    January DECIMAL(10, 2),
    February DECIMAL(10, 2),
    March DECIMAL(10, 2)
);

INSERT INTO Sales_Pivot (Product, January, February, March)
VALUES
    ('Product_A', 1000, 1500, 2000),
    ('Product_B', 1200, 1600, 1800);

-- Dynamic unpivot query
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(Month) 
                    FROM (VALUES ('January'), ('February'), ('March')) AS Months(Month)
                    FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT Product, Month, Revenue
            FROM 
            (
                SELECT Product, January, February, March
                FROM Sales_Pivot
            ) x
            UNPIVOT 
            (
                Revenue FOR Month IN (' + @cols + ')
            ) unpvt'

EXEC sp_executesql @query;

This dynamic unpivot query will unpivot the data to show the revenue for each product in each month.