select all rows for one user in one row mysql

Question

I have a mysql table which has the below structure

id,username,attribute,value,realname,birthdate,phone

and I have these records in the table

1,john,Cleartext-Password,ss,johnsmith,1/1/1990,9786865754
2,john,Expiration,20/1/2018,null,null,null

I want to write a query that shows the below result

username|password|expir     |realname|birthdate|phone 
john    |ss      |20/1/2018 |johnsmith|1/1/1990|9786865754

I tried using the query mentioned below. But I don't seem to get the desired result.

select c1.username ,c1.value as 'password',c1.adsoyad,c1.telefon,c1.email,c1.dtarih,c1.tcno,c2.value as 'expired' from radcheck c1 INNER join radcheck c2 on c1.username=c2.username and c1.attribute='Cleartext-Password' and c2.attribute='Expiration' and c1.username='john' and c2.username='john'

Your two records of sample data do not make any sense to me. By the way, are you storing your dates as text? Don't do that; it will cause problems down the road. — Tim Biegeleisen, Nov 07 '17 at 11:12
don't save plain password. Use password hashing. Also records are in table? Or in text file? Where is db connection code?Please add complete code — Alive to die - Anant, Nov 07 '17 at 11:15
this might be useful https://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table — Cr1xus, Nov 07 '17 at 11:16
Can you paste the actual database contents here from MySQL? The records are pretty confusing — NSP, Nov 07 '17 at 11:16
in my table the attribute can has more than one value for one user (for example :user1, cleartext-password,xxx,user user,1/1/1990,9768756; here the attribute cleartext-password determine user's password in other hand expiration determine when user's account will expir and I want all this info in one row — muhammad ahmed, Nov 07 '17 at 11:24
`select c1.username, c1.value as 'password', c2.value as 'expir', c1.realname, c1.birthdate, c1.phone from radcheck c1 INNER join radcheck c2 on c1.username=c2.username and c1.attribute='Cleartext-Password' and c2.attribute='Expiration' where c1.username='john'` — MIvanIsten, Nov 07 '17 at 11:35
In your example cleartext-password and expiration are in lowercase while in your query they're not. Is it an error in your example ? — hi olaf, Nov 07 '17 at 11:41
thank you for your help but some times user has one attribute Cleartext-Password and don't has Expiration attribute so this query not show any thing and I want to display cleartextpassword and realname and birthdate — muhammad ahmed, Nov 07 '17 at 11:44

score 0 · Accepted Answer · answered Nov 07 '17 at 11:48

Does conditional aggregation do what you want?

select c.username,
       max(case when c.attribute = 'password' then c.value end) as password,
       max(case when c.attribute = 'expiration' then c.value end) as expiration,
       max(c.realname) as realname, min(c.birthdate) as birthdate, max(c.phone) as phone
from radcheck c
group by username

select all rows for one user in one row mysql

1 Answers1