May 15, 2008 at 12:30 am
Hi All,
Please I need your help. Your time is very much appreciated.
I will explain it with a simple example.
Table1:
memberid initialjob
123 GS
124 PDR
Table2:
memberid intermediatejob
123 AAP
125 AV
Table3:
memberid lastjob
123 TS
126 CS
I need to have all these tables into TABLEFINAL:
memberid initialjob intermediatejob lastjob
123 GS AAP TS
124 PDR NULL NULL
125 NULL AV NULL
126 NULL NULL CS
Thank you,
Mary
May 15, 2008 at 1:39 am
HI;
SOLTION 1 . :
DECLARE @t1 TABLE (memberid INT,initialjob VARCHAR(50))
DECLARE @t2 TABLE (memberid INT,intermediatejob VARCHAR(50))
DECLARE @t3 TABLE (memberid INT,lastjob VARCHAR(50))
INSERT INTO @t1
SELECT 123,'GS' UNION
SELECT 124,'PDR'
INSERT INTO @t2
SELECT 123,'AAP' UNION
SELECT 125,'AV'
INSERT INTO @t3
SELECT 123,'TS' UNION
SELECT 126,'CS'
SELECT memberid,initialjob,NULL AS 'intermediatejob' , NULL AS 'lastjob'
FROM @t1
UNION ALL
SELECT memberid,NULL AS 'initialjob',[intermediatejob],NULL AS 'lastjob'
FROM @t2
UNION ALL
SELECT memberid,NULL AS 'initialjob' , NULL AS 'intermediatejob' , lastjob
FROM @t3
May 15, 2008 at 1:57 am
SORRY;
I updated my solution :
DECLARE @t1 TABLE (memberid INT,initialjob VARCHAR(50))
DECLARE @t2 TABLE (memberid INT,intermediatejob VARCHAR(50))
DECLARE @t3 TABLE (memberid INT,lastjob VARCHAR(50))
INSERT INTO @t1
SELECT 123,'GS' UNION
SELECT 124,'PDR'
INSERT INTO @t2
SELECT 123,'AAP' UNION
SELECT 125,'AV'
INSERT INTO @t3
SELECT 123,'TS' UNION
SELECT 126,'CS'
DECLARE @results TABLE(memberid INT,initialjob VARCHAR(50),intermediatejob VARCHAR(50),lastjob VARCHAR(50))
DECLARE @memberid INT,@initialjob VARCHAR(50),@intermediatejob VARCHAR(50),@lastjob VARCHAR(50)
DECLARE myCursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR
(
SELECT memberid,initialjob,NULL AS 'intermediatejob' , NULL AS 'lastjob'
FROM @t1
UNION ALL
SELECT memberid,NULL AS 'initialjob',[intermediatejob],NULL AS 'lastjob'
FROM @t2
UNION ALL
SELECT memberid,NULL AS 'initialjob' , NULL AS 'intermediatejob' , lastjob
FROM @t3
)
OPEN myCursor
FETCH NEXT FROM myCursor INTO @memberid , @initialjob , @intermediatejob,@lastjob
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT * FROM @results WHERE [memberid] = @memberid)
BEGIN
INSERT INTO @results
SELECT @memberid,@initialjob,@intermediatejob,@lastjob
END
ELSE
BEGIN
IF @initialjob IS NOT NULL
UPDATE @results
SET [initialjob] = @initialjob
WHERE [memberid] = @memberid
ELSE IF @intermediatejob IS NOT NULL
UPDATE @results
SET intermediatejob = @intermediatejob
WHERE [memberid] = @memberid
ELSE IF @lastjob IS NOT NULL
UPDATE @results
SET lastjob = @lastjob
WHERE [memberid] = @memberid
END
FETCH NEXT FROM myCursor INTO @memberid , @initialjob , @intermediatejob,@lastjob
END
CLOSE myCursor
DEALLOCATE myCursor
SELECT * FROM @results
May 15, 2008 at 10:57 am
Thank you very much.
May 15, 2008 at 12:26 pm
I've made a modification of Hasan's code to use a UNION of Outer Joins instead of a cursor. I know this one example is small, so it really doesn't matter much. However, if this code will be executed extensively, you can save a few CPU cycles.
DECLARE @t1 TABLE (memberid INT,initialjob VARCHAR(50))
DECLARE @t2 TABLE (memberid INT,intermediatejob VARCHAR(50))
DECLARE @t3 TABLE (memberid INT,lastjob VARCHAR(50))
INSERT INTO @t1
SELECT 123,'GS' UNION
SELECT 124,'PDR'
INSERT INTO @t2
SELECT 123,'AAP' UNION
SELECT 125,'AV'
set statistics time on
INSERT INTO @t3
SELECT 123,'TS' UNION
SELECT 126,'CS'
DECLARE @results TABLE(memberid INT,initialjob VARCHAR(50),intermediatejob VARCHAR(50),lastjob VARCHAR(50))
select isnull(t1.memberid, isnull(t2.memberid, t3.memberid)), t1.initialjob,
t2.intermediatejob, t3.lastjob
from @t1 t1
left outer join @t2 t2
on t1.memberid = t2.memberid
left outer join @t3 t3
on t2.memberid = t3.memberid
UNION
select isnull(t1.memberid, isnull(t2.memberid, t3.memberid)), t1.initialjob,
t2.intermediatejob, t3.lastjob
from @t2 t2
left outer join @t1 t1
on t1.memberid = t2.memberid
left outer join @t3 t3
on t2.memberid = t3.memberid
UNION
select isnull(t1.memberid, isnull(t2.memberid, t3.memberid)), t1.initialjob,
t2.intermediatejob, t3.lastjob
from @t3 t3
left outer join @t2 t2
on t3.memberid = t2.memberid
left outer join @t1 t1
on t2.memberid = t1.memberid
May 15, 2008 at 8:19 pm
Hi ALL,
THANKS A LOT!
May 16, 2008 at 12:39 am
Thanks a lot,Nice approach and Good Implementation without cursor
Rajesh
May 16, 2008 at 6:44 am
Here's a way to do it with a single select and without a cursor or a temporary table.
Insert into TableFinal
select coalesce(t1.memberid,t2.memberid,t3.memberid) 'memberid', initialjob, intermediatejob, lastjob
from @t1 t1
full outer join @t2 t2 on t1.memberid=t2.memberid
full outer join @t3 t3 on t3.memberid=t2.memberid
-- order by 1 /* if this matters for you */
Toni
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply