How to Transpose Rows to Columns in SQL Server?

Transposing Rows to Columns in SQL Server

When working with tabular data, transposing rows into columns can be a useful transformation. In SQL Server, there are several approaches to accomplish this task, depending on the specific requirements.

Conditional Aggregation

One method is to use conditional aggregation. This approach is suitable when the number of columns is known upfront. By using nested CASE statements, you can specify the value to retrieve for each column based on a specific condition. The query below demonstrates conditional aggregation:

SELECT TimeSeconds,
       COALESCE(MAX(CASE WHEN TagID = 'A1' THEN Value END), 'n/a') A1,
       COALESCE(MAX(CASE WHEN TagID = 'A2' THEN Value END), 'n/a') A2,
       COALESCE(MAX(CASE WHEN TagID = 'A3' THEN Value END), 'n/a') A3,
       COALESCE(MAX(CASE WHEN TagID = 'A4' THEN Value END), 'n/a') A4
  FROM table1
 GROUP BY TimeSeconds

PIVOT Operator

Another option is to use the PIVOT operator. This operator allows you to rotate rows into columns based on key values. The following query employs PIVOT to transpose the rows:

SELECT TimeSeconds, A1, A2, A3, A4
  FROM
(
  SELECT TimeSeconds, TagID, Value
    FROM table1
) s
PIVOT
(
  MAX(Value) FOR TagID IN (A1, A2, A3, A4)
) p

Dynamic SQL

If the number of columns is not fixed or is determined dynamically, you can leverage dynamic SQL. This approach involves constructing a SQL query string based on the available TagID values and executing it dynamically. The code below provides an example:

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(TagID)
            FROM Table1
            ORDER BY 1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @sql = 'SELECT TimeSeconds, ' + @cols + '
              FROM
            (
              SELECT TimeSeconds, TagID, Value
                FROM table1
            ) s
            PIVOT
            (
              MAX(Value) FOR TagID IN (' + @cols + ')
            ) p'

EXECUTE(@sql)

These techniques provide various ways to transpose rows into columns in SQL Server. The most suitable approach depends on the nature of the data and the specific requirements of the task.

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