Merging two resultset from same table in sqlserver

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Merging two resultset from same table in sqlserver

Author

Topic

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2008-11-28 : 05:10:53

Hello All,this is the sample schema and query
create table #Temp
(
issueid int not null,
status nvarchar(1),
ownedby nvarchar(15),
[desc] nvarchar(max)
)
insert into #Temp values(1,'A','1','xyz')
insert into #Temp values(2,'A','1','xyz')
insert into #Temp values(41,'A','43','xyz')
insert into #Temp values(56,'A','34','xyz')
insert into #Temp values(57,'I','45','ABC')
insert into #Temp values(58,'I','tr','ABC')
insert into #Temp values(59,'I','rt','ABC')

(select t1.status,t1.ownedby from #Temp t1
where t1.status='A')
(select t2.status,t2.ownedby from #Temp t2
where t2.status='I')
two result sets are returned by the above query .. i want to merge the result sets vertically to display in single result set..
Please help me out..

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2008-11-28 : 05:28:00

the origial resultset is
issueid status ownedby desc
1 A 1 xyz
2 A 1 xyz
41 A 43 xyz
56 A 34 xyz
57 I 45 ABC
58 I tr ABC
59 I rt ABC

i want to display the result set like
desc status ownedby desc status ownedby
xyz A 1 ABC I 45
xyz A 1 ABC I tr
xyz A 43 ABC I rt
xyz A 34 null null null

Please help me out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-28 : 05:40:14

Use UNION ALL for more complicated UNIONS, like this

select	status,
	ownedby
from	#Temp
where	status = 'A'

union all

select	status,
	ownedby
from	#Temp
where	status = 'I'

But in your case, a simple

select	status,
	ownedby
from	#Temp
where	status IN ('A', 'I')

will do.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-28 : 05:43:38

Oh.. You want them aligned HORIZONTALLY?

Ok, then.. What are the rules for binding A and I records together?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 05:51:20

[code]SELECT t1.desc, t1.status,t1.ownedby,
t2.desc, t2.status,t2.ownedby
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY desc ORDER BY issueid) AS Seq,*
FROM Table
WHERE desc='xyz')t1
LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY desc ORDER BY issueid) AS Seq,*
FROM Table
WHERE desc='ABC')t2
ON t2.Seq=t1.Seq[/code]

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2008-11-28 : 06:05:48

Thanks Visakh16, ur query will do the job for me,thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 06:15:45

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources