Can the operation shown in the following image be achieved elegantly in pure SQL and **without hardcoding** names into the statement?

![content of a table as columns in the result](https://i.stack.imgur.com/v4ciV.jpg)

Tables A, B and C are given. The one on the right is the result I would like to get.

The following statement

```
SELECT name, val_name, val
FROM A, B, C
WHERE a_id = A.id
  and b_id = B.id

```

returns the correct information but not in the form I would like:

```
foo|goos|6
foo|blobs|7
bar|goos|8
baz|blobs|9

```