在 SQL 中將行轉換為列

我正在尋找一個查詢來實現以下目標：

目前表格格式

user_id   meta_key    meta_value
-------   --------    ----------
1         address1    abc street
1         city        austin
1         state       TX

期望的輸出

user_id   address1    city         state
-------   --------    ----------   -----
1         abc street  austin       TX  

要將 EAV 表轉置為關係表示，您可以JOIN多次使用源表：

SELECT a.user_id    AS user_id
    , a.meta_value AS address1
    , b.meta_value AS city
    , c.meta_value AS state
 FROM table AS a
 JOIN table AS b  ON b.user_id = a.user_id  
                 AND a.meta_key = 'address1' -- an anchor to restrict the result size
                 AND b.meta_key = 'city'     -- first attribute appended
 JOIN table AS c  ON c.user_id = a.user_id
                 AND c.meta_key = 'state'    -- append the next attribute

-- JOIN table AS d  ON d.user_id = a.user_id
--                 AND d.meta_key = 'someattr'-- append the next attribute...
-- . . . ditto . . . 
;

如果您的 EAV 表包含每個實體的許多屬性，這不是很方便的方法，但可以通過添加序列來自動化JOIN。

在oracle中你可以這樣寫

select u.user_id, 
      a.meta_value as address, 
      c.meta_value as city, 
      s.meta_value as state 
from (select unique user_id from current_table) u,
    current_table a,
    current_table c,
    current_table s 
where u.user_id = a.user_id   
 and a.meta_key = 'address1'   
 and u.user_id = c.user_id   
 and c.meta_key = 'city'   
 and u.user_id = s.user_id   
 and s.meta_key = 'state';

引用自：https://dba.stackexchange.com/questions/117198