CODEWITHSUNDEEP
sql-server
Arif
Arif

Reputation: 67

Data display order when value matches SQL Server

I am trying to display data in a particular way. Now my data looks like - 

ID  |   Name    |   NID
A1      test1       A201
B1      test2       null
C2      test3       null
A201    test1       null

But I want when NID value matches with ID, that matching ID value will display in the next row like below -

ID  |   Name    |   NID
A1      test1       A201
A201    test1       null
B1      test2       null
C2      test3       null

I was thinking to use an array to do this but did not work. I am not sure how can I achieve this. I have tried below -

declare @newID table (s_newID nvarchar(max))
insert @newID (s_newID) select NID from Table1 

select ID, Name, NID from Table1
order by
    case
        when NID IN (select s_newID from @newID) then 1 else 0
    end

Could someone please advise me how can I achieve this.

Thanks

Upvotes: 2

Views: 35

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

select t1.ID, t1.Name, t1.NID
from Table1 t1
left join Table1 t2
on t1.ID = t2.NID
order by
    COALESCE(t2.ID,t1.ID),
    CASE WHEN t1.NID is not null THEN 0 ELSE 1 END

We use a LEFT JOIN to join to our referencing row if we're referenced as a NID. If we are, we use that row's ID as our primary sort condition rather than our own. For the secondary sort, we put items that have a NID value (but are otherwise equal to another row based on our primary sort) first.

Upvotes: 2

Related Questions