How to Transpose Rows into Columns in SQL Server?

Transposing Rows into Columns in SQL Server

In SQL Server, transposing rows into columns can be achieved through various methods. Apart from utilizing cursors to read rows and populate columns, alternative approaches exist for this transformation.

One effective method is conditional aggregation. By leveraging the MAX() function with CASE statements, you can assign values to specific columns based on tag ID, as seen in the following query:

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

Alternatively, you can simplify the query by allowing null values instead of 'n/a':

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

Another method involves the PIVOT operator. It allows you to rotate rows into columns based on specified values:

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

In cases where tag ID values are dynamic, dynamic SQL can be employed to generate the required query. By utilizing the STUFF() and FOR XML PATH() functions, you can build the column list dynamically and use it in a PIVOT query:

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 methods provide efficient ways to transpose rows into columns in SQL Server, catering to different scenarios based on the availability and nature of tag ID values.

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