How to create multiple rows from a initial row

Question

I use mysql db engine, I wonder is it possible that the data in the table one row transferred to another table, this table would consist of two columns, id and value each of the transferred value would go into one row and row would look like ID, value, and for as long as it has a value that is transferred to new row maintains the id as long as it has a value that belonged to the id of a row from which it transferred

Initial table looks like

id  |country_name   |city_1      |city_2      |city_3      |city_4
------------------------------------------------------------------------
1   |Some_country   |some_city1  |some_city2  |some_city3  |some_city4

Wanted table looks like

 id | city_name
 1  |  some_city1
 1  |  some_city2
 1  |  some_city3
 1  |  some_city4

@chambo it's not duplicate, because this is solution for MS SQL, and MySQL doesn't have unpivot function — MPetrovic, Jan 12 '17 at 17:16
my apologies - I did not notice the tag. However it's still a duplicate: http://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table — chambo, Jan 12 '17 at 17:59

Horaciux · Accepted Answer · 2017-01-12T20:52:25.217

1

Use this for one particular ID

select id, city_name from(
    select id, city_1 as city_name from yourTable    
    union all
    select id, city_2 from yourTable    
    union all
    select id, city_3 from yourTable    
    union all
    select id, city_4 from yourTable
) as t where id= yourID

http://sqlfiddle.com/#!9/7ee1f/1

Use this for whole table

 select id, city_name from(
    select id, city_1 as city_name from yourTable    
    union all
    select id, city_2 from yourTable    
    union all
    select id, city_3 from yourTable    
    union all
    select id, city_4 from yourTable
) as t
order by id

edited Jan 12 '17 at 20:52

answered Jan 12 '17 at 17:13

Horaciux

6,322
2
22
41

You should probably throw an ORDER BY id on there. I assume the OP wants to see all the cities for id 1 before moving on to id 2 – kjmerf Jan 12 '17 at 17:46
@Horaciux I only inserted one row of sql, where i say, INSERT INTO myTable(id,city_name) and works smoothly, thank you a lot! – MPetrovic Jan 12 '17 at 17:51
@Horaciux my mistake, this is not totally correct answer, because, if you say where id = 1, it works normal, but if you say where id = id, because i want to do that for whole table, it gives back only city_1, but row has city_1, city_2 ... – MPetrovic Jan 12 '17 at 18:15
@MPetrovic remove the `where` clause – Horaciux Jan 12 '17 at 19:44
@Horaciux I am sorry to bother you, thank you, this is correct, i have totally predicted this – MPetrovic Jan 12 '17 at 20:13

score 0 · Answer 2 · edited May 23 '17 at 12:18

What you are looking for is often referred to as vertical pivoting: you want to pivot something like an array of four city names - hard-wired into the table definition - into four vertical rows.

The solution is a cross join with a temporary table with as many consecutive integers, starting from 1, as you have columns to pivot, in conjunction with a CASE-WHEN expression that makes use of that series of integers.

See here:

WITH foo(id,country_name,city_1,city_2,city_3,city_4) AS (
SELECT 1,'Some_country','some_city1','some_city2','some_city3','some_city4'
)
,    four_indexes(idx) AS (
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
)
SELECT
  id  AS country_id
, idx AS city_id
, CASE idx 
    WHEN 1 THEN city_1
    WHEN 2 THEN city_2
    WHEN 3 THEN city_3
    WHEN 4 THEN city_4
    ELSE ''
  END AS city_name
FROM foo CROSS JOIN four_indexes
;
country_id|city_id|city_name
         1|      1|some_city1
         1|      3|some_city3
         1|      2|some_city2
         1|      4|some_city4

Only the other day, I answered a question that was looking for reversing the operation we are performing here: horizontal pivoting. See here if you're curious ... How to go about a column with different values in a same row in sql?

Happy Playing -

Marco the Sane

thank you a lot for your effort, answer from @Horaciux solved my problem, but also, i would like to test your answer when i solved what is what because it is little bit confusing for me, because i don't understand, but, i will give my best to understand, thank you — MPetrovic, Jan 12 '17 at 17:53
What is it that you're struggling with? The CROSS JOIN? The WITH clause? The CASE expression? All of the above? — marcothesane, Jan 13 '17 at 00:10

How to create multiple rows from a initial row

2 Answers2

Linked