How do I Select distinct rows on multiple columns ignoring relations?-CodePudding

Say we have a table like this:

ID1         ID2         ID3         ID3
----------- ----------- ----------- -----------
101         44          40          46
108         44          37          46
108         44          40          46
101         44          30          36
101         44          30          46
181         44          40          46
397         44          40          46
138         44          40          46
108         44          37          43
108         44          30          46

Desired output is a select query that gives this:

ID1         ID2         ID3         ID4
----------- ----------- ----------- -----------
101         44          30          36
108         NULL        37          43
111         NULL        40          46
138         NULL        NULL        NULL
181         NULL        NULL        NULL
397         NULL        NULL        NULL

I have this select query that almost does what I want:

USE databaseName
DECLARE @start DATE
DECLARE @stop DATE
set @start = '2021-01-01'
set @stop = '2021-12-31'

SELECT DISTINCT t.ID1, NULL ID2, NULL ID3, NULL ID4
FROM ..IdTable t WHERE CreateDate BETWEEN @start AND @stop
UNION
SELECT DISTINCT NULL ID1, t.ID2, NULL ID3, NULL ID4
FROM ..IdTable t WHERE CreateDate BETWEEN @start AND @stop
UNION
SELECT DISTINCT NULL ID1, NULL ID2, t.ID3, NULL ID4
FROM ..IdTable t WHERE CreateDate BETWEEN @start AND @stop
UNION
SELECT DISTINCT NULL ID1, NULL ID2, NULL ID3, t.ID4
FROM ..IdTable t WHERE CreateDate BETWEEN @start AND @stop

It's output:

ID1         ID2         ID3         ID4
----------- ----------- ----------- -----------
NULL        NULL        NULL        36
NULL        NULL        NULL        43
NULL        NULL        NULL        46
NULL        NULL        30          NULL
NULL        NULL        37          NULL
NULL        NULL        40          NULL
NULL        44          NULL        NULL
101         NULL        NULL        NULL
108         NULL        NULL        NULL
111         NULL        NULL        NULL
138         NULL        NULL        NULL
181         NULL        NULL        NULL
397         NULL        NULL        NULL

Can my select query be optimized?
Can a query be created to give my desired output?

CodePudding user response：

Maybe do this using dense_rank()

select ID1,ID2,ID3,ID4
from 
(
Select distinct ID1, r= dense_rank() over (order by ID1 asc) from IdTable 
)T1
full join 
(
Select distinct ID2, r=dense_rank() over (order by ID2 asc) from IdTable 
)T2 on T1.R=T2.R 
full join 
(
Select distinct ID3, r=dense_rank() over (order by ID3 asc) from IdTable 
)T3 on T1.R=T3.R
full join 
(
Select distinct ID4, r=dense_rank() over (order by ID4 asc) from IdTable 
)T4 on T1.R=T4.R