How to Dynamically Pivot Rows to Columns in SQL Without Aggregation?

This SQL code dynamically pivots rows to columns without losing data types, a feat often requiring aggregation functions. Let's rephrase it for clarity and improved readability.

Dynamically Pivoting Rows to Columns in SQL: A Type-Preserving Solution

Standard SQL PIVOT operations typically demand an aggregate function (like MAX or MIN), which can lead to data loss or type coercion. This example demonstrates a dynamic SQL approach to pivot rows into columns while preserving the original data types, handling up to 12 test names.

The challenge lies in transforming a table with a variable number of test names (up to 12) and mixed data types into a table where each test name becomes a column. A dynamic solution is necessary to accommodate this variability.

The Dynamic SQL Solution

This solution cleverly uses dynamic SQL to construct the pivot query based on the data itself.

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

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

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

EXECUTE(@query);

The code first builds a comma-separated list of column names (@cols) from the unique TEST_NAME values. Then, it constructs the PIVOT query (@query), using MAX(val) as the aggregation function. While MAX is used, because we are pivoting on unique values within each SBNO, this effectively preserves the original data. Finally, the generated query is executed.

Illustrative Output

The output table will have a column for each unique test name, populated with the corresponding values from the original table, maintaining their original data types:

<code>| SBNO | TEST1 | TEST2 |  TEST3 |
---------------------------------
|    1 | 0.304 |   2.3 |   PASS |
|    2 |  0.31 |   2.5 |   PASS |
|    3 | 0.306 |   2.4 | (null) |</code>

This method efficiently handles varying numbers of test names and diverse data types, offering a robust solution for dynamic pivoting in SQL. Remember to replace yourtable with the actual name of your table.

The above is the detailed content of How to Dynamically Pivot Rows to Columns in SQL Without Aggregation?. For more information, please follow other related articles on the PHP Chinese website!