Solutions to some problems in the DB2 database (1)

In the process of using the DB2 database, some problems are inevitable. This article summarizes several common DB2 database problems for your reference.

1. Use SQL to implement cross tabulation.

SQL Server provides the limit keyword to help implement cross-tabulation, but DB2 does not seem to have an equivalent value. The implementation method is slightly more complex. The following is an example.

SELECT location,
MAX (case when metric = 'ph' THEN value END) as PH,
MAX (case when metric = 'Hg 'THEN value END) as Hg,
MAX (case when metric = 'fe 'THEN value END) as Fe,
MAX (case when metric = 'mn 'THEN value END) as Mn
FROM data_view
Group by location

The data_view table/view has three fields: location, metric, and value. The data is as follows:

Location1 PH 1.142575278063135
Location1 Hg 0.0018310994140037379.
Location1 Fe 0.2974983113601452
Fig 1 Mn 0.09548843270448454
Location2 PH 6.096025515217242
Location2 Hg 0.005575948698159294
Location2 Fe 0.32657056877142043
Location2 Mn 0.009418315198912612
The resulting crosstab chart:

Location ph Hg Fe Mn
Location1 1.142575278063135 0.0018310994140037379 0.2974983113601452
Location2 6.096025515217242 0.005575948698159294 0.32657056877142043