Mysql result by half

Question

i want split my result to half and add now row like this

main Table

item  qty  rate
===============
test1  10   22
test2  11   25
test3  12   22
test4  14   50
test5  16   26
test6  55   20
test7  60   60

i want like this

item  qty  rate   item1  qty1  rate1
====================================
test1  10   22    test2   16    26
test3  11   25    test4   16    26
test5  12   22    test6   16    26
test7  14   50    test8   16    26

or

 item  qty  rate   item1  qty1  rate1
    ====================================
    test1  10   22   test5  12   22
    test2  16   26   test6  16   26
    test3  11   25   test7  14   50 
    test4  16   26   test8  16   26

Why do you want to do this? This will complicate the query logic a lot, and make it a lot longer to execute — piotrgajow, May 25 '16 at 11:45
I meant that the select query will be a lot more complicated. — piotrgajow, May 25 '16 at 11:53
If your problem is the size of the table, try to partition it. — Robert Kock, May 25 '16 at 12:12
Displaying a report (or any data) is the job of the client. You can just place two rows side by side there (or ask how you can do it there). The database is the wrong place to do that kind of stuff. — Solarflare, May 25 '16 at 16:28
Take a look at [this question](http://stackoverflow.com/questions/4064794/mysql-enumeration-rownum-odd-and-even-records). It is about selecting odd and even rows. You can left join them — Rik, May 26 '16 at 07:40

score 1 · Accepted Answer · edited May 26 '16 at 07:33

This will take time depending on the size of the table. The solution is...

SELECT ROUND(COUNT(*)/2) INTO @cnt FROM tobehalf;

SELECT t1.item, t1.qty, t1.rate, t2.item as item1, t2.qty as qty1, t2.rate as rate1 FROM
(
SELECT * FROM (SELECT tobehalf.*, @rownum1 := @rownum1 + 1 AS rank
FROM tobehalf, (SELECT @rownum1 := 0) r1) d1
WHERE d1.rank <= @cnt
) t1
LEFT JOIN
(
SELECT * FROM (SELECT tobehalf.*, @rownum2 := @rownum2 + 1 AS rank
FROM tobehalf, (SELECT @rownum2 := 0) r2) d2
WHERE d2.rank > @cnt
) t2 on t1.rank = t2.rank - @cnt

Mysql result by half

1 Answers1