Create view with one row for each column in a table-CodePudding

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!

CodePudding user response：

Are you looking for this?

WITH
-- your input 
indata(id,hwerohwsd,dwerwefwf,wfsdwg,fwegwg) AS (
          SELECT 1,0.867,1.5345,123.63,0.9283
UNION ALL SELECT 2,0.523,1.3456,341.63,3.3495
UNION ALL SELECT 1,0.342,1.1467,121.63,4.9852
)
-- real query starts here, replace following comma with "WITH" ...
,
-- need to pivot four columns in this example:
-- four consecutive integers ...
i(i) AS (
            SELECT 1
  UNION ALL SELECT 2
  UNION ALL SELECT 3
  UNION ALL SELECT 4
)
SELECT
  id  
, i  AS curr_id
 -- currency symbol seems to depend on the position of the input column,
 -- not on the input column name - so hard-wiring i <-> currency symbol correspondence
, CASE i
    WHEN 1 THEN 'EUR'
    WHEN 2 THEN 'JPY'
    WHEN 3 THEN 'GBP'
    WHEN 4 THEN 'CHF'
  END AS currency
, CASE i
    WHEN 1 THEN hwerohwsd
    WHEN 2 THEN dwerwefwf
    WHEN 3 THEN wfsdwg
    WHEN 4 THEN fwegwg
  END AS rate
FROM indata CROSS JOIN i
ORDER BY 1;
-- out  id | curr_id | currency |   rate   
-- out ---- --------- ---------- ----------
-- out   1 |       1 | EUR      |   0.8670
-- out   1 |       2 | JPY      |   1.5345
-- out   1 |       3 | GBP      | 123.6300
-- out   1 |       4 | CHF      |   0.9283
-- out   1 |       1 | EUR      |   0.3420
-- out   1 |       2 | JPY      |   1.1467
-- out   1 |       3 | GBP      | 121.6300
-- out   1 |       4 | CHF      |   4.9852
-- out   2 |       1 | EUR      |   0.5230
-- out   2 |       2 | JPY      |   1.3456
-- out   2 |       3 | GBP      | 341.6300
-- out   2 |       4 | CHF      |   3.3495

CodePudding user response：

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