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
Angela4evaAsked:
Who is Participating?
 
Vadim RappConnect With a Mentor Commented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ThomasianConnect With a Mentor Commented:
SELECT ID
      ,MAX(rank1) Rank1
      ,MAX(rank2) Rank2
      ,MAX(rank3) Rank3
FROM tablename
GROUP BY ID

Open in new window

0
 
Wayne BarronAuthor, Web DeveloperCommented:
@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
 
Angela4evaAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.