August 19, 2015 at 4:47 am
If object_id('tempdb ..#sem01') is not null
drop table tempdb ..#sem01
create TABLE #sem01 (StudentID INT, majorsubject VARCHAR(20))
INSERT INTO #sem01(StudentID, majorsubject) SELECT 1,'Physics'
INSERT INTO #sem01(StudentID, majorsubject) SELECT 2,'Chemistry'
INSERT INTO #sem01(StudentID, majorsubject) SELECT 3,'Maths'
if object_id('tempdb ..#sem02') is not null
drop table tempdb ..#sem02
create TABLE #sem02 (StudentID INT, minorsubject VARCHAR(20))
INSERT INTO #sem02(StudentID, minorsubject) SELECT 1,'Biology'
INSERT INTO #sem02(StudentID, minorsubject) SELECT 1,'Computer Science'
INSERT INTO #sem02(StudentID, minorsubject) SELECT 2,'Botany'
INSERT INTO #sem02(StudentID, minorsubject) SELECT 2,'Zoology'
if object_id('tempdb ..#sem03') is not null
drop table tempdb ..#sem03
create TABLE #sem03 (StudentID INT, optional VARCHAR(20))
INSERT INTO #sem03(StudentID, optional) SELECT 1,'Environment'
INSERT INTO #sem03(StudentID, optional) SELECT 1,'Sanskrit'
INSERT INTO #sem03(StudentID, optional) SELECT 1,'GK'
INSERT INTO #sem03(StudentID, optional) SELECT 2,'History'
--** Output Required
StudentID|majorsubject|minorsubject| optional
1 | Physics| Biology | Environment
1| Physics| Computer Science | Sanskrit
1 | Physics| NULL | GK
2 | Chemistry | Botany| History
2 | Chemistry | Zoology| NULL
3 | Maths | NULL | NULL
August 19, 2015 at 5:14 am
NM, didn't read
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2015 at 12:42 am
Hi Guys Finally i got solution:-)
SELECT ISNULL(s12.StudentID, s13.StudentID) AS StudentID,
ISNULL(s12.majorsubject, s13.majorsubject) AS majorsubject,
s12.minorsubject, s13.optional
FROM
(
SELECT s1.StudentID, s1.majorsubject, s2.minorsubject,
ROW_NUMBER() OVER (PARTITION BY s1.StudentID, s1.majorsubject ORDER BY s1.StudentID, s1.majorsubject) AS rnMS
FROM #sem01 s1
LEFT JOIN #sem02 s2 ON s1.StudentID = s2.StudentID
) s12
FULL JOIN
(
SELECT s1.StudentID, s1.majorsubject, s3.optional,
ROW_NUMBER() OVER (PARTITION BY s3.StudentID, s1.majorsubject ORDER BY s3.StudentID, s1.majorsubject) AS rnMS
FROM #sem01 s1
LEFT JOIN #sem03 s3 ON s1.StudentID = s3.StudentID
) s13 ON
s12.StudentID = s13.StudentID and s12.majorsubject = s13.majorsubject
and s12.rnMS = s13.rnMS
ORDER BY StudentID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply