August 24, 2006 at 9:21 am
Following are 2 tables I want to join to product a single output
TABLE 1
---------------------------
IDNumberDTName
11NULLa
21NULLb
32NULLc
45NULLd
TABLE 2
--------------------------
IDNumberDTName
111NULLaa
122NULLbb
132NULLcc
143NULLdd
154NULLee
I want to join the data from the 2 given tables such that for every number matches exactly once and any leftovers get a set of null values
i.e. the output will look like this
OUTPUT
----------------------------
| ID | Number | DT | Name | ID | Number | DT | Name | 1 | 1 | NULL | a | 11 | 1 | NULL | aa | 2 | 1 | NULL | b | - | - | - | - | 3 | 2 | NULL | c | 12 | 2 | NULL | bb | - | - | NULL | - | 13 | 2 | NULL | cc | - | - | NULL | - | 14 | 3 | NULL | dd |
|---|
| - | - | NULL | - | 15 | 4 | NULL | ee |
| 4 | 5 | NULL | d | - | - | - | - |
August 24, 2006 at 10:42 am
declare @Table1 table
(
IDint,
Numberint,
DTvarchar(10),
[Name]varchar(10))
insert into @table1 values (1, 1, NULL, 'a')
insert into @table1 values (2, 1, NULL, 'b')
insert into @table1 values (3, 2, NULL, 'c')
insert into @table1 values (4, 5, NULL, 'd')
declare @Table2 table
(
IDint,
Numberint,
DTvarchar(10),
[Name]varchar(10)
)
insert into @table2 values (11, 1, NULL, 'aa')
insert into @table2 values (12, 2, NULL, 'bb')
insert into @table2 values (13, 2, NULL, 'cc')
insert into @table2 values (14, 3, NULL, 'dd')
insert into @table2 values (15, 4, NULL, 'ee')
selecta.ID, a.Number, a.DT, a.[Name],
b.ID, b.Number, b.DT, b.[Name]
from
(select rank() over (Partition by number order by name) rank,
ID, Number, DT, [Name] from @table1) as a
left outer join ( select rank() over (Partition by number order by name) rank,
ID, Number, DT, [Name] from @table2) as b
on a.number = b.number
and a.rank = b.rank
union
selecta.ID, a.Number, a.DT, a.[Name],
b.ID, b.Number, b.DT, b.[Name]
from
(select rank() over (Partition by number order by name) rank,
ID, Number, DT, [Name] from @table1) as a
right outer join ( select rank() over (Partition by number order by name) rank,
ID, Number, DT, [Name] from @table2) as b
on a.number = b.number
and a.rank = b.rank
August 25, 2006 at 1:08 am
If you don't have SQL 2005, this might do the trick.
-- prepare test data
declare @Table1 table (ID int, Number int, DT varchar(10), [Name] varchar(10))
insert into @table1 values (1, 1, NULL, 'a')
insert into @table1 values (2, 1, NULL, 'b')
insert into @table1 values (3, 2, NULL, 'c')
insert into @table1 values (4, 5, NULL, 'd')
declare @Table2 table (ID int, Number int, DT varchar(10), [Name] varchar(10))
insert into @table2 values (11, 1, NULL, 'aa')
insert into @table2 values (12, 2, NULL, 'bb')
insert into @table2 values (13, 2, NULL, 'cc')
insert into @table2 values (14, 3, NULL, 'dd')
insert into @table2 values (15, 4, NULL, 'ee')
-- stage the data
DECLARE @Stage TABLE (Row INT IDENTITY(0, 1), ID1 INT, Number1 INT, DT1 VARCHAR(10), Name1 VARCHAR(10), ID2 INT, Number2 INT, DT2 VARCHAR(10), Name2 VARCHAR(10))
INSERT @Stage (ID1, Number1, DT1, Name1, ID2, Number2, DT2, Name2)
SELECT t1.ID,
t1.Number,
t1.DT,
t1.[Name],
t2.ID,
t2.Number,
t2.DT,
t2.[Name]
FROM @Table1 t1
FULL JOIN @Table2 t2 ON t2.Number = t1.Number
-- remove duplicates
UPDATE s
SET s.ID1 = NULL,
s.Number1 = NULL,
s.DT1 = NULL,
s.Name1 = NULL
FROM @Stage s
WHERE (SELECT COUNT(*) FROM @Stage t WHERE t.id1 = s.id1 AND t.Row < s.Row) > 0
UPDATE s
SET s.ID2 = NULL,
s.Number2 = NULL,
s.DT2 = NULL,
s.Name2 = NULL
FROM @Stage s
WHERE (SELECT COUNT(*) FROM @Stage t WHERE t.id2 = s.id2 AND t.Row < s.Row) > 0
-- show the result
SELECT ID1 ID,
Number1 Number,
DT1 DT,
Name1 [Name],
ID2 ID,
Number2 Number,
DT2 DT,
Name2 [Name]
FROM @Stage
N 56°04'39.16"
E 12°55'05.25"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply