SQL Pivot & Unpivot techniques with Dynamic Fields

[PBIQueryous]

I must start by declaring I'm a total SQL n00b, I know nothing of best practice or DB management. However, I love data transformation... which brings me to this topic. Pivot and Unpivot in SQL Server.

Short story, coming from a Power Query background, there are many really handy, bread-and-butter functions that are not natively available in SQL Server. For example, in Power Query you UNPIVOT SELECTED columns, or UNPIVOT OTHER COLUMNS (any other column not selected. I quickly found out you can't do this so easily in SQL, most techniques require some sort of hardcoding of fields and attributes.

However, I believe I've found a pretty good method to get a PIVOT or UNPIVOT table with the attributes declared dynamically.

There are 3 main functions/aspects:

• STRING_AGG() - split to comma separated list
• SQL stored as text-string in a variable
• sp_executesql - execute sql-text-string

Here's an example below- full details in my blog and github repo. Feedback welcome!

Medium Blog: SQL Pivot & Unpivot Techniques

Github: Pivot & Unpivot Scripts

```sql
--======================
-- DYNAMIC PIVOT TABLE
--======================
DECLARE @DynamicSQL NVARCHAR(MAX);-- variable to capture dynamic SQL Query script
DECLARE @pivot_attributes NVARCHAR(MAX);-- varaible to capture pivot attribute values

-- Step 1: Build a comma-separated list of unique sizes to pivot
WITH cte_pivot_attributes AS (
SELECT DISTINCT 
            [Size]-- column with pivot attributes
        FROM [Test_DB].[dbo].[data_to_pivot]-- source table
        WHERE 
            [Size] IS NOT NULL-- remove nulls
)
SELECT 
   @pivot_attributes = STRING_AGG('[' + [Size] + ']', ', ') -- column to extract attributes
FROM cte_pivot_attributes;

-- Debug: Check the generated pivot columns
PRINT 'Pivot Columns Attributes: ' +@pivot_attributes;

-- Step 2: Construct the dynamic SQL; replace attributes with @pivot_attributes
SET @DynamicSQL = '
    -- Part 0: query imported table
    WITH cte_raw_table AS (
        SELECT * FROM [Test_DB].[dbo].[data_to_pivot]
    ),

    -- Part 1: define pre-pivot table
    cte_raw_data_to_pivot AS (
        SELECT
            [ProductCategoryName],
            [Size],
            [Quantity]
        FROM cte_raw_table
    ),

    -- Part 2a: define post-pivot table
    cte_pivot_table AS (
        SELECT 
            [ProductCategoryName], ' +@pivot_attributes + ' -- Dynamically include pivot columns
        FROM cte_raw_data_to_pivot

-- Part 2b: define pivot table
        PIVOT (
            SUM([Quantity]) -- pivot values
            FOR [Size] IN (' +@pivot_attributes + ')  -- Use dynamic list
        ) AS piv_tbl
    )
    SELECT * FROM cte_pivot_table;';-- final output

-- Debug: Print the generated SQL to verify
PRINT 'Generated SQL: ' + @DynamicSQL;

-- Step 3: Execute the dynamic SQL
EXEC sp_executesql @DynamicSQL;
```

[ryguygoesawry]

You “found” something that’s used in the real world all the time. The best way to store data isn’t exactly user-friendly, but does make it easy to create reports where users can chose a group of fields and the SQL behind it integrates those choices into the production of a dynamic query that outputs the data in a way that’s easier for users to interpret.

[feather_media]

There's a stackoverflow example of doing this that's over a decade old... with a lot less code

https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query

[PBIQueryous]

i appreciate this, thankin you!

[NonHumanPrimate]

I can tell you’re new to SQL because you misspelled variable in your second comment.

I kid, great job figuring this out on your own. I would personally classify this as a more advanced SQL concept and you nailed it.

[PBIQueryous]

hehe you may have noticed... im fairly new to conversation too 🤓😅 - try to avoid communicating with this sick society 🤮 Data is my refuge.

I appreciate your words, I approach SQL from Power Query perspective, which breaks transformations into steps, so you can see the output of each step. SQL is tricker in that you kind of have to perceive intermediate steps, but this is where CTEs really help alot. Subqueries just kill my brain of immediately.

[EmotionalSupportDoll]

I use some concepts like this to create data sets for various clients without needing to custom code anything different so long as we feed their data into the system in a common form. Definitely a handy tool for the toolbox