CODEWITHSUNDEEP
mysqlsql
MPetrovic
MPetrovic

Reputation: 171

How to create multiple rows from a initial row

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

Upvotes: 0

Views: 683

Answers (2)

Horaciux
Horaciux

Reputation: 6477

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

Upvotes: 1

marcothesane
marcothesane

Reputation: 6731

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

Upvotes: 0

Related Questions