Mysql通过合并多列创建新列并显示结果

Question

我正在处理一个数据集:“data_1”，通过将多个列及其值合并到该单列中来创建新列“类别”。

例如，data_1:

user_id | family | friend | roommate | college
===============================================
1002345 |    1   |    0   |     1    |    0
-----------------------------------------------
1002346 |    0   |    1   |     0    |    1
-----------------------------------------------
  ...   |   ...  |   ...  |   ...    |   ...
------------------------------------------------

我在 Mysql 中尝试过“Case When”或“Unpivot”功能，但它们不起作用。

select 
  user_id, 
  category 
from data_1 
unpivot (category for col_name in (
               data_1.family,
               data_1.friend,
               data_1.roommate,
               data_1.college)
          )

预期输出表:

user_id | Category | Value
============================
1002345 | Family   |    1   
----------------------------
1002345 | Friend   |    0
----------------------------
1002345 | Roommate |    1
----------------------------
1002345 | College  |    0
----------------------------    
1002346 | Family   |    0   
----------------------------
1002346 | Friend   |    1
----------------------------
1002346 | Roommate |    0
----------------------------
1002346 | College  |    1
---------------------------- 
  ...   |   ...    |   ... 
----------------------------

谢谢!

Answer 1

我们可以使用UNION ALL集合运算符来组合多个查询的结果。

我们可以执行JOIN 操作，从表中的每一行生成四行。像这样的事情:

SELECT t.user_id 
     , q.category       AS `Category`
     , CASE q.category 
       WHEN 'Family'    THEN t.family 
       WHEN 'Friend'    THEN t.friend
       WHEN 'Roommate'  THEN t.roommate
       WHEN 'College'   THEN t.college
       END AS `Value` 
  FROM data_1 t
 CROSS
  JOIN ( SELECT 'Family'  AS category
         UNION ALL SELECT 'Friend'
         UNION ALL SELECT 'Roommate'
         UNION ALL SELECT 'College'
       ) q
 ORDER
    BY t.user_id 
     , q.category='College'
     , q.category

或者，我们可以这样做:

( SELECT t1.user_id
       , 'Family'       AS `Category`
       , t1.family      AS `Value`
    FROM data_1 t1
)
UNION ALL
( SELECT t2.user_id
       , 'Friend'
       , t2.friend
    FROM data_1 t2
)
UNION ALL
( SELECT t3.user_id
       , 'Roommate'
       , t3.roommate
    FROM data_1 t3
)
UNION ALL
( SELECT t4.user_id
       , 'College'
       , t4.college
    FROM data_1 t4
)
ORDER BY user_id, `Category`='College',`Category`