How to Transpose Rows into Columns in DB2 Using PIVOT (Implicit)?

Transposing Data Structure with Pivoting in DB2

The task requires transposing rows into columns in a DB2 table, transforming the data from the following structure:

ItemID    Item    Value
---------------------
1     Meeting     Now
1     Advise      Yes
1     NoAdvise    No
2     Meeting     Never
2     Advise      No
2     NoAdvise    Null
2     Combine    Yes

into the desired structure:

ItemID    Meeting  Advise   NoAdvise 
---------------------------------------
1         Now      Yes       No
2         Never    No        Null

To achieve this transformation, you can use a SQL query that employs the PIVOT operator, which enables the transposition of rows into columns based on a specified categorization field. Here's an example of a query that can be used in DB2 LUW:

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

In this query, the PIVOT operator is not explicitly used, but it is achieved implicitly through the use of the CASE statement inside the MAX() aggregate function. The CASE statement acts as a way to categorize the rows and return the maximum value for each category. The MAX() aggregate function then ensures that the maximum value is returned for each category.

Note that the "Combine" column is not included in the transposed result as per the requirement. The resulting data will be transposed into columns as desired.

The above is the detailed content of How to Transpose Rows into Columns in DB2 Using PIVOT (Implicit)?. For more information, please follow other related articles on the PHP Chinese website!