sqldatabase

Easiest 'SQL way' to combine a column with a horizontal list

I have a column like this:

price
4.99
3.99
1.99
...

I want to get a matrix of price * tax_rate at various levels, the levels would be:

[.05, .10, .15]

So I'd like to basically do matrix multiplication on the 'column' (table column) with the 'row' (list). What's the best way to do this in SQL?

It should be N columns wide where N=length of the list.

Expected result (column names don't matter)

 price   price_05  price_10  price_15
│  1.99 ┆ 0.0199 ┆ 0.0995 ┆ 0.2985 │
│  3.99 ┆ 0.0399 ┆ 0.1995 ┆ 0.5985 │
│  4.99 ┆ 0.0499 ┆ 0.2495 ┆ 0.7485 │
│ 17.99 ┆ 0.1799 ┆ 0.8995 ┆ 2.6985
Solution

  • A canonical ANSI SQL way to do this would be to first create a bona fide second table containing the tax rates:

    table: rates
name | tax_rate
r1   | 0.01
r2   | 0.05
r3   | 0.15

    Then take the cross product of the two tables, aggregate by price, and pivot out the individual rates into separate columns:

    SELECT
    p.price,
    MAX(CASE WHEN r.name = 'r1' THEN p.price * r.tax_rate END) AS "0.01",
    MAX(CASE WHEN r.name = 'r2' THEN p.price * r.tax_rate END) AS "0.05",
    MAX(CASE WHEN r.name = 'r3' THEN p.price * r.tax_rate END) AS "0.15"
FROM prices p
CROSS JOIN rates r
GROUP BY p.price
ORDER BY p.price;

    Note that you must use an exact numeric type for the tax_rate column in order for the above CASE expressions to work properly. This is because floating point arithmetic is not exact by default.

    Demo