Pivot
在 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';