How to Perform Dynamic Pivoting in SQL Without Aggregation?

Dynamic Pivot: cleverly transform rows into columns

Dynamic SQL statements are powerful tools for creating flexible queries. This article will focus on converting rows into columns without using aggregate functions.

Understanding dynamic perspective

Pivot operations usually use aggregate functions such as SUM or MAX. However, in some cases it is desirable to obtain the value perspective directly. This requires a slightly different approach.

For example, consider the following source table:

<code>╔═══════════╦══════╦═══════╗
║ 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  ║
╚═══════════╩══════╩═══════╝</code>

The desired output is a pivot table similar to the following:

<code>╔══════════════════════════╗
║ SBNO Test1 Test2   Test3 ║
╠══════════════════════════╣
║ 1    0.304  2.3    PASS  ║
║ 2    0.31   2.5    PASS  ║
║ 3    0.306  2.4    NULL  ║
╚══════════════════════════╝</code>

Dynamic pivoting without aggregate functions

To achieve this result without using aggregate functions, we can utilize the PIVOT function. Since the VAL column contains varchar data, we can utilize the MAX aggregate function.

Hardcoded perspective

For a limited number of tests, we can hardcode the column names as follows:

<code class="language-sql">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;</code>

Scalable dynamic perspective

For large volumes of testing, Dynamic SQL provides a scalable solution:

<code class="language-sql">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)</code>

Both methods will produce the same desired output. This example demonstrates how to leverage dynamic SQL to create a flexible and efficient solution to solve complex data processing tasks.

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