Mysql create a new column by merge multiple columns and diplay result

Question

I'm working on a dataset: 'data_1', to create a new column 'Category' by merging multiple columns into this single column as well as its value.

For example, data_1:

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

I have tried 'Case When' or 'Unpivot' function in Mysql, but they are not working.

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)
          )

Expected Output table:

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
---------------------------- 
  ...   |   ...    |   ... 
----------------------------

Thanks!

Possible duplicate of [MySQL - turn table into different table](https://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table) — Paul Spiegel, May 21 '19 at 17:13

score 0 · Accepted Answer · answered May 21 '19 at 17:22

We can use UNION ALL set operator to combine results from multiple queries.

We could do a JOIN operation, to produce four rows from each row in the table. Something like this:

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

Or, we could do something like this:

( 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`

Mysql create a new column by merge multiple columns and diplay result

1 Answers1