Click here to Skip to main content
15,067,335 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
   
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
   
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.
   
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