alternative to full join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

alternative to full join

Author

Topic

fluffywalrus
Starting Member

1 Post

Posted - 2011-10-01 : 13:12:06

Any help would be appreciated with the following:

I have several tables that all have the same two columns, position and value, such that position is the key and both are non null. Position is simply the row number starting at 1 and value is text of variable length. I am looking for an alternative to nesting full joins due to running out of memory in trying to create the result set below.

T1

1 A
2 B
3 D

T2

1 B
2 C
3 E

T3

1 B
2 F

result set

T1value T2value T3value
A null null
B B B
null C null
D null null
null E null
null null F

My goal is to line up any overlaps between two or more tables while keeping each column in the result set in ascending order by the corresponding table's position. I am completely open to changing the original tables if that makes sense. One attempt that partially worked in Oracle was to union all the individual tables and then use a combination of fname and the decode function to distribute the unionized table across the desired columns. Again any ideas are appreciated and if any clarification would help ask away.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-01 : 13:54:11

[code]
DECLARE @T1 table
(
position int,
val char(1)
)
insert @T1
SELECT 1, 'A' union all
SELECT 2, 'B' union all
SELECT 3, 'D'

DECLARE @T2 table
(
position int,
val char(1)
)
insert @T2

SELECT 1, 'B' union all
SELECT 2, 'C' union all
SELECT 3, 'E'

DECLARE @T3 table
(
position int,
val char(1)
)
insert @T3
SELECT 1, 'B' union all
SELECT 2, 'F'

SELECT [T1value],[T2value],[T3value]
FROM
(
SELECT DENSE_RANK() OVER (ORDER BY val) AS Rnk,val,cat
FROM
(
SELECT val,'T1value' AS Cat
FROM @T1
UNION ALL
SELECT val,'T2value'
FROM @T2
UNION ALL
SELECT val,'T3value'
FROM @T3
)t
)r
PIVOT (MAX(Val) FOR Cat IN ([T1value],[T2value],[T3value]))p

output
-----------------------------------------
T1value T2value T3value
A NULL NULL
B B B
NULL C NULL
D NULL NULL
NULL E NULL
NULL NULL F

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources