15,506,343 members
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 1:10am
v2
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..

## Solution 3

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```

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!

## Solution 4

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```

## Solution 2

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.

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

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