Dynamic Pivot Transformation in SQL: Row-to-Column Conversion Without Aggregation

Fundamental Principles of SQL Pivot Transformation

In database operations, data pivoting is a common transformation technique that converts row data into column data for easier data analysis and reporting. Traditional pivot operations typically require aggregate functions such as SUM, AVG, or COUNT, but in certain scenarios, simple value transformation without aggregation is needed.

Problem Scenario Analysis

Consider a test data table containing three key fields: TEST_NAME (test name), SBNO (serial number), and VAL (test value). The VAL field may contain multiple data types, including integers, decimals, and strings. The objective is to transform each TEST_NAME value into independent columns, creating a pivot table with SBNO as rows.

Example source data table:

TEST_NAME | SBNO | VAL
----------|------|-----
Test1     | 1    | 0.304
Test1     | 2    | 0.31
Test1     | 3    | 0.306
Test2     | 1    | 2.3
Test2     | 2    | 2.5
Test2     | 3    | 2.4
Test3     | 1    | PASS
Test3     | 2    | PASS

Static Pivot Implementation

When the number of test names is fixed and known, static SQL statements can be used to implement pivot transformation. Although SQL Server's PIVOT function requires aggregation operations, the MAX or MIN functions can achieve the effect of no actual aggregation, as each combination of TEST_NAME and SBNO is unique in the source table.

Implementation code:

SELECT sbno, Test1, Test2, Test3
FROM
(
  SELECT test_name, sbno, val
  FROM yourtable
) d
PIVOT
(
  MAX(val)
  FOR test_name IN (Test1, Test2, Test3)
) piv;

The working principle of this code is: first prepare data through a subquery, then use the PIVOT function to transform test_name values into column names, with MAX(val) ensuring each cell contains only one value. Due to data uniqueness, the MAX function does not perform actual aggregation calculations.

Dynamic Pivot Implementation

In practical applications, the number of test names may vary, potentially up to 12 different values. Dynamic SQL is required to build pivot statements in such cases. Dynamic SQL allows us to generate SQL statements at runtime based on actual data.

Implementation steps:

1. Build column name string: Use STUFF and FOR XML PATH techniques to dynamically generate a list of all test name column names
2. Construct dynamic SQL statement: Embed the column name string into the pivot query
3. Execute dynamic SQL: Use EXECUTE or sp_executesql to execute the generated statement

Complete implementation code:

DECLARE @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX)

-- Dynamically generate column name list
SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(TEST_NAME)
    FROM yourtable
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    , 1, 1, '')

-- Build dynamic SQL query
SET @query = 'SELECT sbno, ' + @cols + '
FROM (
    SELECT test_name, sbno, val
    FROM yourtable
) x
PIVOT (
    MAX(val)
    FOR test_name IN (' + @cols + ')
) p'

-- Execute dynamic query
EXECUTE(@query)

Technical Points Analysis

1. Role of QUOTENAME Function
When handling dynamic SQL, the QUOTENAME function ensures column names are properly quoted, preventing SQL injection attacks and naming conflicts. It surrounds column names with square brackets, such as [Test1].

2. FOR XML PATH Technique
This technique is used to concatenate multiple rows of data into a single string. By specifying an empty path (PATH('')), XML tags can be avoided, directly obtaining the concatenated string.

3. Usage of STUFF Function
The STUFF function is used to remove extra commas at the beginning of strings. It starts from the first character, deletes one character (comma), and replaces it with an empty string.

4. Data Type Handling
Since the VAL field contains mixed data types, using the MAX function can properly handle all types. For string types, MAX returns the maximum value in alphabetical order; for numeric types, it returns the actual maximum value.

Performance Optimization Recommendations

1. Index Optimization: Create composite indexes on TEST_NAME and SBNO fields to improve query performance.

2. Dynamic SQL Caching: If test names don't change frequently, consider caching generated dynamic SQL statements to avoid repeated construction.

3. Parameterized Queries: In production environments, using sp_executesql instead of EXECUTE is recommended for better execution plan reuse.

Extended Application Scenarios

This dynamic pivot technique is not only applicable to test data but can also be applied to:

• Sales data pivoting by month/quarter
• User attribute horizontal expansion
• Multi-language text conversion
• Configuration parameter table transformation

By flexibly adjusting column generation logic and pivot conditions, various complex data transformation requirements can be accommodated.