Search code examples
sqlsql-serverinner-join

unable to swap rows in sql

I have the below table where names will be inserted first. Then I will get IDs where I need to map with names

     ID  NAME
    null   Test1
    null    Test2
    1      null
    2      null

here

I need the result like

ID  NAME
1    Test1
2    Test2

I tried below query but it doesn't work for me

select t1.ID , t2.Name from table1 T1 join table1 t2 on T1.id = t2.id
Solution

  • According to screen short you are working with SQL Server , you could try cte expression which may help you

    ;with cte as
(
    select max(id) id, row_number() over (order by (select 1)) rn from
    ( 
       select *, rank() over(order by id) rnk from table
    ) a
    group by a.rnk 
    having max(id) is not null
), cte1 as
(
    select max(name) name, row_number() over (order by (select 1)) rn from
    ( 
       select *, rank() over(order by name) rnk from table
    ) a
    group by a.rnk 
    having max(name) is not null
)
select c.id, c1.name from cte c
join cte1 c1 on c1.rn = c.rn

    Result :

    id  name
1   test1
2   test2