How to Transpose Rows into Columns in DB2?

Transposing Rows into Columns in DB2

When working with data in a database, it may be necessary to transpose rows, meaning to flip the rows and columns. This can be useful for data analysis, reporting, and other purposes.

If you have a DB2 table with rows that you want to transpose into columns, there are several methods you can use. One approach is to use correlated subqueries, which can be efficient for small datasets.

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

This query uses correlated subqueries to find the maximum value for each item in each row. The MAX() function is used to aggregate the values, and the CASE statement is used to determine which column to assign the value to.

Another method for transposing rows into columns is to use a combination of aggregation and pivot functions. This approach can be more efficient for large datasets, as it uses a single SQL statement to perform the transposition.

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 (
    SELECT ItemID, Item, Value
    FROM TableName
) AS t
GROUP BY ItemID
PIVOT (MAX(Value) FOR Item IN ('Meeting', 'Advise', 'NoAdvise'))

This query uses the PIVOT() function to transpose the data. The PIVOT() function takes two arguments: the column to pivot on (in this case, Item), and the list of values to include in the new columns (in this case, Meeting, Advise, and NoAdvise).

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