Angela4eva
asked on
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
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
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Thomasian
Simple and easy.
Will have to remember this one!
Carrzkiss
Simple and easy.
Will have to remember this one!
Carrzkiss
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
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