Multiply a Row by a Multiplier of a Column

I have a query that output a dataset that looks like this in DB2:

I'd like to add a nested query with this condition: When Multiplier value is greater than 0, multiply each row of record by the numbers in Multiplier column then add a unique suffix number to the ID column while keeping everything else the same. The output should be like this:

The easiest way would be to generate a series of all possible suffixes,
then join your table to this series (left join, so that Multiplier = 0 still gets a row on output).

No need to have one series per row: just compute one series up to the maximum value of Multiplier, and in the join condition take only the items of the series below the row's Multiplier.

Now you'll have as many rows as Multiplier tells.

Then format your suffixes by padding each series' value against the Multiplier's length (when converted to string): thus if your Multiplier is 123, you'll lpad(x, length('123')), that is, lpad(x, 3): be x = 123, x = 85, or x = 3, you'll get your aligned suffixes 123, 085, 003.

with
  serie(n) as
  (
    select max(Multiplier) n from t
    union all
    select n - 1 from serie where n > 1
  )
select
  t.ID||case when t.Multiplier > 1 then lpad(serie.n, length(''||t.Multiplier), '0') else '' end as ID,
  t.Amount, t.Multiplier
from t left join serie on serie.n <= Multiplier
order by t.ID, serie.n;

(as seen in this fiddle, where I included another example row with Multiplier = 12 to show 2-characters suffixes)

Getting really unique IDs

As shown in my comment, if you've got another row with ID = 41, Multiplier = 0, it will output another row with ID 41… the same as the result of concatenating ID 4 with a suffix of 1!

To prevent this, it would be wise to replace length(''||t.Multiplier) with length(''||(select max(t.Multiplier) from t),
or its value precomputed in another CTE.

Here is the adapted version:

with
  maxi as (select coalesce(length(''||max(Multiplier)), 0) lmax from t),
  serie(n) as
  (
    select max(Multiplier) n from t
    union all
    select n - 1 from serie where n > 1
  )
select
  t.ID||lpad(coalesce(serie.n, 0), lmax, '0') as ID,
  t.Amount, t.Multiplier
from t
join maxi on true
left join serie on serie.n <= Multiplier
order by t.ID, serie.n;

which returns:

(last query of this fiddle)