• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

sql query modify the layout

I have a table that has the following date
rank1        rank2   rank3           ID
1      NULL      NULL      112
NULL      2      NULL      112
NULL      NULL      NULL      112

I want to write a query that returns

ID   Rank1 Rank2 Rank3
112  1         2            Null
0
Angela4eva
Asked:
Angela4eva
2 Solutions
 
Wayne BarronCommented:
I am not sure about how to get it all on a single row, will have to continue looking in on it.
But to get everything that is not null.

from table where rank1 is not null or rank2 is not null or rank3 is not null.

That will return all rows, but to do it as a single row, that I am unsure of?

Carrzkiss
0
 
dougaugCommented:
See if this works for you:


select ID,
       (select Rank1 from table t2 where Rank1 is not null and t2.ID = t1.ID) as Rank1,
       (select Rank2 from table t3 where Rank2 is not null and t3.ID = t1.ID) as Rank2,
       (select Rank3 from table t4 where Rank3 is not null and t4.ID = t1.ID) as Rank3
  from table t1
0
 
ThomasianCommented:
SELECT ID
      ,MAX(rank1) Rank1
      ,MAX(rank2) Rank2
      ,MAX(rank3) Rank3
FROM tablename
GROUP BY ID

Open in new window

0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Wayne BarronCommented:
@Thomasian
Simple and easy.
Will have to remember this one!

Carrzkiss
0
 
LowfatspreadCommented:
did you want the max?
or something else

if you had
rank1        rank2   rank3           ID
1      NULL      NULL      112
NULL      2      NULL      112
NULL      NULL      NULL      112
3    null null 112

what would you want?

3 2 null 112  or something else
0
 
Vadim RappCommented:
select
nullif(0,max(isnulll(rank1,0))),
nullif(0,max(isnulll(rank2,0))),
nullif(0,max(isnulll(rank3,0)))

from mytable
group by id
0
 
Angela4evaAuthor Commented:
thanks
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now