Pivoting in DB2

by

The currently accepted answer by bhamby is certainly correct, but it’s worth checking if using several correlated subqueries is much slower than a single group by (hint: it most likely is):

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

It’s also a bit simpler in my opinion

SQLFiddle (in PostgreSQL, but works on DB2 LUW as well)

More Related Contents:

  1. SQL Server dynamic PIVOT query?
  2. PostgreSQL Crosstab Query
  3. Simple way to transpose columns and rows in SQL?
  4. Dynamic alternative to pivot with CASE and GROUP BY
  5. T-SQL dynamic pivot
  6. SQL Server 2005 Pivot on Unknown Number of Columns
  7. How to pivot in SQLite or i.e. select in wide format a table stored in long format?
  8. TSQL Pivot without aggregate function
  9. DB2 Comma Separated Output by Groups
  10. SQL query to pivot a column using CASE WHEN
  11. Pivoting rows into columns dynamically in Oracle
  12. Pivoting data in MS Access
  13. SQL Server : dynamic pivot over 5 columns
  14. Pivot Table and Concatenate Columns
  15. Dynamic Oracle Pivot_In_Clause
  16. Dynamic Pivot Table in SQL Server
  17. Does DB2 have an “insert or update” statement?
  18. Using pivot on multiple columns of an Oracle row
  19. PIVOT query on Distinct records
  20. INNER JOIN in UPDATE sql for DB2
  21. Using PIVOT in SQL Server 2008
  22. Oracle Dynamic Pivoting
  23. SQL Transpose Rows as Columns
  24. How to convert Rows to Columns in Oracle? [duplicate]
  25. Regular Expressions in DB2 SQL
  26. SQL server join tables and pivot
  27. How to transform vertical data into horizontal data with SQL?
  28. DB2 Query to retrieve all table names for a given schema
  29. Flattening of a 1 row table into a key-value pair table
  30. How to check db2 version

Leave a Comment