Create view with one row for each column in a table

I have a table that looks like this:

id | hwerohwsd | dwerwefwf | wfsdwg | fwegwg

1  | 0.867     | 1.5345    | 123.63 | 0.9283
2  | 0.523     | 1.3456    | 341.63 | 3.3495
1  | 0.342     | 1.1467    | 121.63 | 4.9852

I need a view with 3 columns, that has a row for each column in the original table, so it would look like this:

id | currency | rate
1  | EUR      | 0.867
2  | JPY      | 1.5345
3  | GBP      | 123.63
4  | CHF      | 0.9283

The values on the currency column in the view need to be created by me (the EUR, JPY, etc) and are fixed values; I need to get the view rate from the first row of the original table, ignoring all other rows.

I’m stuck on this for a while now, and have no idea how I can solve this (did a lot of research but no joy). Can some one help me here? Thanks in advance!

Answer

The simplest approach, assuming you have another column we can order by (to identify which row is ‘first’) would be…

WITH
  first_row AS
(
  SELECT *
    FROM your_table
ORDER BY id, something_else
   LIMIT 1
)
          SELECT 1 AS id, 'EUR' AS currency, hwerohwsd AS rate FROM first_row
UNION ALL SELECT 2,       'JPY',             dwerwefwf         FROM first_row
UNION ALL SELECT 3,       'GBP',             wfsdwg            FROM first_row
UNION ALL SELECT 4,       'CHF',             fwegwg            FROM first_row