CODEWITHSUNDEEP
mysql
dipti
dipti

Reputation: 162

Want to get repeated data in a single row

I want to get repeated rows in a single row,below is my table

monthly_attendance

enter image description here

My query:

SELECT distinct a.* 
FROM monthly_attendance a 
WHERE a.month = 9
    AND a.year = 2018 
    AND a.batch in (5) 
    AND a.student_id=257

But shows in multiple rows, below is my result:

enter image description here

Upvotes: 1

Views: 61

Answers (2)

Strawberry
Strawberry

Reputation: 33935

Note that a normalised data set might look this - although there may of course be yet further opportunities for normalisation...

student_id batch roll_no ym         a
       257    27    2601 2018-09-00  4 
       257    27    2601 2018-09-00  6
       257    27    2601 2018-09-00  8 
       257     1    2601 2018-09-00 11
       257     5    2601 2018-09-00 12
       257     5    2601 2018-09-00 14 
       257     5    2601 2018-09-00 17
       257     5    2601 2018-09-00 19

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

you should use a (fake) aggregation function for reduce the rows 

select student_id
    , batch
    , roll_no
    , month
    , year
    , min(a1)
    , min(a2)
    , min(a3)
    , min(a4)
    , min(a5)
    , min(a6)       
    , min(a7)
    , min(a8)
    , min(a9)
    , min(a10)
    , min(a11)
    , min(a12)      
    , min(a13)
    , min(a14)
    , min(a15)
    , min(a16)
    , min(a17)
    , min(a18)      
    , min(a19)  
from monthly_attendance a 
WHERE a.month = 9 
    AND a.year = 2018 
        AND a.batch in (5) 
            and a.student_id=257
GROUP BY student_id
    , batch
    , roll_no
    , month
    , year

Upvotes: 1

Related Questions