CODEWITHSUNDEEP
javascriptmysqljson
Pranav
Pranav

Reputation: 972

Split joined result set to rows

Have a stored procedure which process records from certain tables. To store some result generated by join operation there is a temporary table.

Table A

+----+------+--------+
| id | name | number |
+----+------+--------+
|  1 | John |    123 |
|  2 | Tim  |    567 |
|  3 | Bill |    789 |
|  4 | Jim  |    345 |
+----+------+--------+

Table B

+----+------+--------+
| id | code | number |
+----+------+--------+
|  1 | LK   |    123 |
|  2 | CN   |    123 |
|  3 | BN   |    789 |
|  4 | IN   |    345 |
+----+------+--------+

Table Temp

+----+------+-----+------+--------+
| id | name | age | code | number |
+----+------+-----+------+--------+
|  1 | John |  54 | LK   |    123 |
|  1 | John |  54 | CK   |    123 |
|  3 | Bill |  26 | BN   |    789 |
|  4 | Jim  |  78 | IN   |    345 |
+----+------+-----+------+--------+

Converted the table Temp result set to JSON.

[{"id":1,"name":"John","code":"LK","number":123}, {"id":2,"name":"John","code":"CK","number":123}, {"id":3,"name":"Bill","code":"BN","number":789}, {"id":4,"name":"Jim","code":"IN","number":345}]

I need to split the records to show it on view as below.

+------------+-----+------+--------+
|    name    | age | code | number |
+------------+-----+------+--------+
| John       | 54  |      |        |
|            |     | LK   |    123 |
|            |     | CK   |    123 |
| Bill       |  26 |      |        |
|            |     | BN   |    789 |
| Jim        |  78 |      |        |
|            |     | IN   |    345 |
+------------+-----+------+--------+


[{"name":"John","age":54}, {"code":"LK","number":123}, {"code":"CK","number":123}, {"name":"Bill","age":26}, {"code":"BN","number":789}, {"name":"Jim","age":78}, {"code":"IN","number":345}]

What is an effective way, split the JSON or is it possible to generate such result set by querying table Temp in MySQL?

Upvotes: 1

Views: 107

Answers (2)

Dylan Su
Dylan Su

Reputation: 6065

-- query wanted
select 
    if(name = @last_name, '', @last_name := name) as name,
    if(age = @last_age, '', @last_age := age) as age,
    code,
    number
from
(   
    (select name, age, code, number from t)
    union 
    (select distinct name, age, '', '' from t)
    order by 1,2,3,4
) as t2 cross join (select @last_name := null, @last_age := null ) param;

Demo:

SQL:

-- data
create table t(id int, name char(20), age int, code char(20), number int);
insert into t values
(  1 , 'John' ,  54 , 'LK'   ,    123 ),
(  1 , 'John' ,  54 , 'CK'   ,    123 ),
(  3 , 'Bill' ,  26 , 'BN'   ,    789 ),
(  4 , 'Jim'  ,  78 , 'IN'   ,    345 );
select * from t;

-- query wanted
select 
    if(name = @last_name, '', @last_name := name) as name,
    if(age = @last_age, '', @last_age := age) as age,
    code,
    number
from
(   
    (select name, age, code, number from t)
    union 
    (select distinct name, age, '', '' from t)
    order by 1,2,3,4
) as t2 cross join (select @last_name := null, @last_age := null ) param
;

Output:

mysql> select * from t;
+------+------+------+------+--------+
| id   | name | age  | code | number |
+------+------+------+------+--------+
|    1 | John |   54 | LK   |    123 |
|    1 | John |   54 | CK   |    123 |
|    3 | Bill |   26 | BN   |    789 |
|    4 | Jim  |   78 | IN   |    345 |
+------+------+------+------+--------+
4 rows in set (0.00 sec)

mysql> -- query wanted
mysql> select
    -> if(name = @last_name, '', @last_name := name) as name,
    -> if(age = @last_age, '', @last_age := age) as age,
    -> code,
    -> number
    -> from
    -> (
    -> (select name, age, code, number from t)
    -> union
    -> (select distinct name, age, '', '' from t)
    -> order by 1,2,3,4
    -> ) as t2 cross join (select @last_name := null, @last_age := null ) param
    -> ;
+------+------+------+--------+
| name | age  | code | number |
+------+------+------+--------+
| Bill | 26   |      |        |
|      |      | BN   | 789    |
| Jim  | 78   |      |        |
|      |      | IN   | 345    |
| John | 54   |      |        |
|      |      | CK   | 123    |
|      |      | LK   | 123    |
+------+------+------+--------+
7 rows in set (0.00 sec)

Upvotes: 1

Shadow
Shadow

Reputation: 34232

To me this seems more like a formatting the output question, rather than how to query the database, so I would rather use js on the client side to format the output in this way.

If I really wanted to use sql, then honestly, I would not use the temp table. I would rather create another query based on the 2 original tables with a union and order the final resultset. All you would have to do on the client side is to hide the id column:

(select id, name, age, null as code, null as number
 from a)
union
(select id, null, null, code, number
 from b)
order by id asc, name desc

Upvotes: 1

Related Questions