How to Pivot Rows into Columns in DB2 Using Dynamic SQL?

Transposing Rows into Columns in DB2 Using Dynamic SQL

The objective is to pivot data in a table, converting rows into columns, to obtain a desired output. Consider the following table structure:

Source Table

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

Desired Output

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

SQL Query Using Correlated Subqueries

One approach to achieve this transformation is by using correlated subqueries:

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 executes multiple correlated subqueries, one for each desired output column, to extract the corresponding value for each row. The results are then grouped by the ItemID column to obtain the pivoted output.

Alternative Approach Using Dynamic SQL

Another option is to leverage dynamic SQL to construct a pivot table. This approach allows for more flexibility and can be customized to generate the desired output columns:

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

SET @cols = ''
SELECT @cols += ',MAX(CASE WHEN Item = ''' + Item + ''' THEN Value END) AS ' + Item
FROM (
  SELECT DISTINCT Item
  FROM A
) AS Items

SET @sql = 'SELECT ItemID, ' + SUBSTRING(@cols, 2) + '
            FROM A
            GROUP BY ItemID'

EXEC (@sql)

This query dynamically constructs the SQL statement based on the distinct values in the Item column and executes it, generating the pivoted output.

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