show column into the raw

Question

Imagine the Table bellow: Client:

ID  Name       Family     Child1   Age1   Child2   Age2   Child3   Age3
1   Andy       Constant   Robbert   2     Elina     5     Sofia    1

2   Doro       Panayi     Costas    4

What i would like to get is below resault.

Name     age    ParentName          ID
Robert   2      Andy Constantin      1
Elina    5      Andy Constantin      1
Sofia    1      Andy Constantin      1
Costas   4      Doros Panayi         2

I would appreciate any Help

Please provide your attempt to solve this problem so we can help you best. — Xaphas, Oct 04 '19 at 12:01
I have tried many ways but they didnt work at all. I already search the web and most solutions are converting rows to columns. — iman, Oct 04 '19 at 12:02
If you're looking really for a fix to your issue. Then fix your design, because e it's the real problem. — Ilyes, Oct 04 '19 at 12:02
Unfortunately i cant change the design as i dont have have access to that, — iman, Oct 04 '19 at 12:03
The standard way to unpivot in mysql is to union - you should find examples if you search for mysql unpivot. — P.Salmon, Oct 04 '19 at 12:06
See this question/answer: https://stackoverflow.com/questions/15648160/mysql-convert-column-into-rows — Turophile, Oct 04 '19 at 12:15

score 1 · Answer 1 · answered Oct 04 '19 at 12:23

Not that difficult for a limited number of children (and probably not that onerous for even larger families)

drop table if exists t;
create table t
(ID int, Name varchar(20), Family varchar(20), Child1 varchar(20), Age1 int,  Child2 varchar(20), Age2 int,
  Child3 varchar(20),   Age3 int);
insert into t values
(1  , 'Andy'   ,    'Constant' ,  'Robbert'  , 2  ,   'Elina'  ,   5  ,   'Sofia' ,   1),
(2  , 'Doro'   ,    'Panayi'   ,  'Costas'   , 4  ,   null, null,null,null);

select id,name,family, child1,age1 from t where child1 is not null
union all
select id,name,family, child2,age2 from t where child2 is not null
union all
select id,name,family, child3,age3 from t where child3 is not null;

+------+------+----------+---------+------+
| id   | name | family   | child1  | age1 |
+------+------+----------+---------+------+
|    1 | Andy | Constant | Robbert |    2 |
|    2 | Doro | Panayi   | Costas  |    4 |
|    1 | Andy | Constant | Elina   |    5 |
|    1 | Andy | Constant | Sofia   |    1 |
+------+------+----------+---------+------+
4 rows in set (0.00 sec)

I am assuming that if the child name is null then getting the age is pointless..

show column into the raw

1 Answers1