StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is, I think, what you want, but it would be better to consider database design, because this is a spreadsheet-like solution.</p> <pre><code>SELECT t0.a, t0.b, t0.c, t0.d, Iif(Isnull([a1]), 0, [a1]) AS an, Iif(Isnull([b1]), 0, [b1]) AS bn, Iif(Isnull([c1]), 0, [c1]) AS cn, Iif(Isnull([d1]), 0, [d1]) AS dn, Iif(Isnull([e1]), 0, [e1]) AS en FROM (((((SELECT DISTINCT t.a, t.b, t.c, t.d FROM table3 t) AS t0 LEFT JOIN (SELECT t.a, t.b, t.c, t.d, t.f AS a1 FROM table3 t WHERE t.e = "A") AS a0 ON ( t0.d = a0.d ) AND ( t0.c = a0.c ) AND ( t0.b = a0.b ) AND ( t0.a = a0.a )) LEFT JOIN (SELECT t.a, t.b, t.c, t.d, t.f AS b1 FROM table3 t WHERE t.e = "B") AS b0 ON ( t0.d = b0.d ) AND ( t0.c = b0.c ) AND ( t0.b = b0.b ) AND ( t0.a = b0.a )) LEFT JOIN (SELECT t.a, t.b, t.c, t.d, t.f AS c1 FROM table3 t WHERE t.e = "C") AS c0 ON ( t0.d = c0.d ) AND ( t0.c = c0.c ) AND ( t0.b = c0.b ) AND ( t0.a = c0.a )) LEFT JOIN (SELECT t.a, t.b, t.c, t.d, t.f AS d1 FROM table3 t WHERE t.e = "D") AS d0 ON ( t0.d = d0.d ) AND ( t0.c = d0.c ) AND ( t0.b = d0.b ) AND ( t0.a = d0.a )) LEFT JOIN (SELECT t.a, t.b, t.c, t.d, t.f AS e1 FROM table3 t WHERE t.e = "E") AS e0 ON ( t0.d = e0.d ) AND ( t0.c = e0.c ) AND ( t0.b = e0.b ) AND ( t0.a = e0.a ); </code></pre> <p><strong>Table3</strong></p> <pre><code>ID a b c d e f 1 1 2 3 4 a €10.00 2 1 2 3 4 b €10.00 3 1 2 3 4 c €10.00 4 1 2 3 4 d €10.00 5 1 2 3 4 e €10.00 6 1 2 3 5 a €10.00 7 1 2 3 5 b 8 1 2 3 5 c €10.00 9 1 2 3 5 d €10.00 10 1 2 3 5 e €10.00 </code></pre> <p><strong>Result</strong></p> <p>There are two rows, because there are only two different sets in the first four columns.</p> <pre><code>a b c d an bn cn dn en 1 2 3 4 €10.00 €10.00 €10.00 €10.00 €10.00 1 2 3 5 €10.00 €0.00 €10.00 €10.00 €10.00 </code></pre> <p>The way the sql above is supposed to work, is that it selects each of the four definition columns and the currency column from the table where the sort column has a particular sort letter and labels the currency column with the sort letter, each of these sub queries are then assembled, however, you can take a sub query and look at the results. The last one is the part between the parentheses:</p> <pre><code>INNER JOIN (SELECT t.a, t.b, t.c, t.d, t.f AS e1 FROM table3 t WHERE t.e = "E") AS e0 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload