How to Pivot Rows to Columns in SQL Without Aggregation?

SQL row to column operation without aggregate function

In data processing, converting row data into column data (row to column) is a common need, which is especially useful for summarizing or analyzing data based on multiple attributes.

The PIVOT function in SQL can implement row to column operations. However, the PIVOT function often needs to be combined with an aggregate function (such as MAX or MIN) to handle the pivoted values. But in some cases we want to do simple value pivoting without any aggregation operations.

For example, consider a table named "TEST" with the following columns:

• TEST_NAME
• SBNO
• VAL

VAL columns contain various data types (integer, decimal, or varchar). The goal is to transform the TEST_NAME column into three separate columns (Test1, Test2 and Test3) without any aggregation:

<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>

<code>目标输出

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

To achieve this without using any aggregate functions, we can use dynamic 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)

This query generates a dynamic SQL statement that contains all unique values ​​of TEST_NAME as column names. It then uses the PIVOT function to convert the row data into columns without applying any aggregation.

Running this SQL will produce the desired output, which is a table with pivot columns Test1, Test2 and Test3 containing the corresponding VAL values ​​from the original table.

This revised output maintains the original meaning while using slightly different wording and sentence structure. The technical details remain unchanged.

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