Click here to Skip to main content
15,867,986 members
Please Sign up or sign in to vote.
4.00/5 (2 votes)
See more:
sir, i have a sql query that give output like :-

SQL QUERY:
SQL
select courseid,semid,roomno,studentid from matrix
where courseid=1493 and semid=3
union all
select courseid,semid,roomno,studentid from matrix
where courseid=1493 and semid=5



Output:-
SQL
CourseID  Semid Roomno StudentID
 [1493]          [3]  [A-307]     [1012]
 [1493]          [3]  [A-307]     [1013]
 [1493]          [3]  [A-307]     [1014]   
 [1493]          [5]  [B-405]     [1145]  
 [1493]          [5]  [B-405]     [1146]

but i need output like :-

SQL
CourseID  SemID RoomNo StudentID   CourseID SemID  RoomNo StudentID
 [1493]   [3]  [A-307]     [1012]   [1493]      [5]      [B-405]     [1145]
 [1493]   [3]  [A-307]     [1013]   [1493]      [5]      [B-405]     [1146] 
 [1493]   [3]  [A-307]     [1014]

how can i do it
Posted
Updated 18-Nov-14 0:10am
v2
Comments
Bernhard Hiller 18-Nov-14 3:00am    
It is not at all clear how you want to join the two sets of data - why do you say
[1493] [3] [A-307] [1012] [1493] [5] [B-405] [1145]
but not
[1493] [3] [A-307] [1012] [1493] [5] [B-405] [1146]?
RossMW 18-Nov-14 3:15am    
Why not just use a join rather than Union?
Shweta N Mishra 18-Nov-14 3:22am    
what is the data contained in each table ?, Di you tried Join on both table
TCS54321 18-Nov-14 5:25am    
this is a single table. and i tried join but its not give right output..

What about this?

SQL
SELECT
    R1.CourseId,
    R1.SemId,
    R1.RoomNo,
    R1.RowNumber,
    R2.CourseId,
    R2.SemId,
    R2.RoomNo,
    R2.RowNumber
FROM
    (SELECT *, ROW_NUMBER() OVER (ORDER BY CourseId) 'RowNumber' FROM #Matrix WHERE CourseId = 1493 and SemId = 3) AS R1
    FULL OUTER JOIN
    (SELECT *, ROW_NUMBER() OVER (ORDER BY CourseId) 'RowNumber' FROM #Matrix WHERE CourseId = 1493 and SemId = 5) AS R2
    ON
    R1.RowNumber = R2.RowNumber
 
Share this answer
 
Comments
King Fisher 18-Nov-14 6:45am    
interesting My 5+ :)
Manas Bhardwaj 18-Nov-14 6:46am    
Thanks :)
Shweta N Mishra 18-Nov-14 6:57am    
+5
/\jmot 18-Nov-14 8:06am    
cool +5
Manas Bhardwaj 18-Nov-14 8:28am    
thx!
Helo Dude Try This ....

SQL
Declare @matrix AS TABLE (courseid NVARCHAR(10),semid  NVARCHAR(10),roomno  NVARCHAR(10),studentid  NVARCHAR(10))


INSERT INTO  @matrix (courseid,semid,roomno,studentid) VALUES
                      ('[1493]','[3]','[A-307]','[1012]')
INSERT INTO  @matrix (courseid,semid,roomno,studentid) VALUES
                      ('[1493]','[3]','[A-307]','[1013]')
INSERT INTO  @matrix (courseid,semid,roomno,studentid) VALUES
                      ('[1493]','[3]','[A-307]','[1014]')

INSERT INTO  @matrix (courseid,semid,roomno,studentid) VALUES
                      ('[1493]','[5]','[B-407]','[1045]')
INSERT INTO  @matrix (courseid,semid,roomno,studentid) VALUES
                      ('[1493]','[5]','[B-407]','[1046]')



;WITH Hierarchy(CourseID3,SemID3,RoomNo3,StudentID3,CourseID5,SemID5,RoomNo5,StudentID5)
AS
(
SELECT courseid,semid,roomno,studentid,'','','','' FROM @matrix WHERE semid = '[3]'
union ALL
SELECT '','','','',courseid,semid,roomno,studentid FROM @matrix WHERE semid ='[5]'

)


SELECT CourseID3 [courseid],
       SemID3 [semid],
       RoomNo3 [roomno],
       StudentID3 [studentid],
       CourseID5 [courseid],
       SemID5 [semid],
       RoomNo5 [roomno],
       StudentID5 [studentid]  FROM Hierarchy
 
Share this answer
 
You could do PIVOT after getting Union result, which you used earlier

check this .need help in sql query..[^]

and other way is

SQL
SELECT  CourseID=MAX(A.CourseID), SemID=MAX(a.SemID), RoomNo=mAX(a.roomNO), A.StudentID ,
CourseID=MAX(B.CourseID), SemID=MAX(B.SemID), RoomNo=mAX(B.roomNO),B.StudentID
FROM
(
select courseid,semid,roomno,studentid from #matrix
where courseid=1493 and semid=3
) A
full outer join
(
select courseid,semid,roomno,studentid from #matrix
where courseid=1493 and semid=5
) b on A.courseid=b.courseid --AND A.SEMID=b.SEMID
gROUP BY A.StudentID,B.StudentID



But it would not give you exact result you wanted.
 
Share this answer
 
Comments
TCS54321 18-Nov-14 6:42am    
tnx for ur effort shweta ji.. i am using both solutions. but not getting right output.
Manas Bhardwaj 18-Nov-14 6:43am    
Hi Shewta, This is not what OP wants. Please see my answer.
King Fisher 18-Nov-14 6:45am    
Instead of your Query you can do this way :)

select *From (select courseid,semid,roomno,studentid from matrix
where semid=3)t1 ,

(select courseid,semid,roomno,studentid from matrix
where semid=5)t2
Shweta N Mishra 18-Nov-14 6:57am    
Ok yes you can try this


select *
FROM
(
select courseid,semid,roomno,studentid,ROW_NUMBER() Over ( Order BY studentid) RowID from #matrix
where courseid=1493 and semid=3
) A
full outer join
(
select courseid,semid,roomno,studentid,ROW_NUMBER() Over ( Order BY studentid) RowID from #matrix
where courseid=1493 and semid=5
) b on A.RowID=b.RowID --AND A.SEMID=b.SEMID

and i see you already have same solution from manas.
my +5 to him

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900