How to Pivot Rows into Columns in DB2 Using GROUP BY?

Pivoting in DB2

This question revolves around transposing rows into columns in a DB2 table. The provided data is structured with ItemID, Item, and Value columns. The objective is to convert this data into a transposed format, where the values for Meeting, Advise, and NoAdvise are arranged as columns, with the ItemID serving as the row identifier.

One approach to execute this pivot is to utilize a GROUP BY clause along with multiple MAX() expressions. By employing MAX() and specifying the criteria for each Item type within the CASE statement, the corresponding values can be retrieved. For instance:

SELECT 
  ItemID,
  MAX(CASE WHEN Item = 'Meeting'  THEN Value END) AS Meeting,
  MAX(CASE WHEN Item = 'Advise'   THEN Value END) AS Advise,
  MAX(CASE WHEN Item = 'NoAdvise' THEN Value END) AS NoAdvise
FROM table_name
GROUP BY ItemID

This query generates the desired transposed format, where ItemID serves as the primary key and Meeting, Advise, and NoAdvise become the column headers. The GROUP BY clause ensures that the data is grouped by ItemID, providing a distinct row for each item.

Alternatively, the same result can be achieved by utilizing multiple correlated subqueries as suggested by the currently accepted answer in this forum. However, it's noteworthy that the subquery approach may encounter performance issues compared to the more efficient GROUP BY solution, especially with larger datasets.

The above is the detailed content of How to Pivot Rows into Columns in DB2 Using GROUP BY?. For more information, please follow other related articles on the PHP Chinese website!