how to show multiple row data into in a row mysql

Question

hi guys i am new to learn MySQL i have this table

f_id|c_file|e.name|operation|rate|c_no | qty|
 1    015    A      coping   0.30  1    1300
 2    015    A      coping   0.30  2    4567
 3    015    A      coping   0.30  3    1300
 4    015    A      coping   0.30  4    789
 5    015    A      coping   0.30  5    47
 6    015    B      cutting  0.30  1    568
 7    015    B      cutting  0.30  2    123
 8    015    B      cutting  0.30  3    8952
 9    015    B      cutting  0.30  1    456
 10   015    B      cutting  0.30  2    89
 11   015    B      cutting  0.30  3    78

now i want table like in this form of table table2

f_id|c_file|e.name|operation|c_no1|c_no2 |c_no3|c_no4|c_no5|total|rate|total*rate
 2     015  B       cutting 568     123   8952              9643  0.30 2892.9
 2     015  B       cutting 456     89    78                9643  0.30 2892.9
 total                      1024    212   9030

You can simple select multiple tables. `SELECT table1.*, table2.* FROM table1, table2` — Nicolas, Feb 14 '17 at 14:29
This is called pivot table and have been asked & answered many time here on SO. — Shadow, Feb 14 '17 at 14:30
The linked duplicate describes both static (number of additional fields is known in advance) and dynamic pivoting (number of fields is not known in advance) in MySQL. — Shadow, Feb 14 '17 at 14:33
If the number of columns is dynamic, you are best pivoting in client code. See also [_Auto generate pivot SELECT_](http://mysql.rjweb.org/doc.php/pivot) — Rick James, Feb 15 '17 at 23:42
as i mention i am new in mysql can you give easy way to fulfill this problem — user3602024, Feb 16 '17 at 14:12

Gurwinder Singh · Accepted Answer · 2017-02-14T16:26:02.987

0

You can use aggregation to do the pivoting and user variables to generate the sequence number.

set @fid := 0;

select @fid := @fid + 1 fid,
    c_file,
    e.name,
    operation,
    sum(case when c_no = 1 then qty end) c_no1,
    sum(case when c_no = 2 then qty end) c_no2,
    sum(case when c_no = 3 then qty end) c_no3,
    sum(case when c_no = 4 then qty end) c_no4,
    sum(case when c_no = 5 then qty end) c_no5,
    sum(qty) total,
    rate,
    sum(qty) * rate total_times_rate
from t
group by c_file,
    e.name,
    operation,
    rate
order by min(f_id);

edited Feb 14 '17 at 16:26

answered Feb 14 '17 at 14:31

Gurwinder Singh

38,557
6
51
76

thanks alot sir ,can you tell me one thing more that how to sum all the columns like(c_no1+c_no2+c_no3+c_no4+c_no5) please... – user3602024 Feb 14 '17 at 15:28
@user3602024 - Please see the edit – Gurwinder Singh Feb 14 '17 at 15:31
thanks again gurv sir.... – user3602024 Feb 14 '17 at 15:35
sir this query is not working for adding the value of columns !! can you write again full query with sum – user3602024 Feb 14 '17 at 16:08
sir i have also edited the question sir kindly give the answer i know i am going too much worst but i have to ask – user3602024 Feb 14 '17 at 16:23
@user3602024 - Updated the answer for you – Gurwinder Singh Feb 14 '17 at 16:26
thanks sir alot ..... – user3602024 Feb 14 '17 at 16:28

how to show multiple row data into in a row mysql

1 Answers1