joining two results - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

joining two results

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-23 : 21:02:36

Hi below is my two separate queries that hits the same table but produces two different results.

select GroupName [MainGroup] from Groups where IdGroup= 0;
select GroupName [SubGroup] from Groups where IdGroup <> 0;

how to show both the result set? i couldn't try with union all.

Any suggestions or samples please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-23 : 22:16:53

how do you want to show both the result ?

Why do you mean by " i couldn't try with union all" ?

KH
[spoiler]Time is always against us[/spoiler]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-23 : 22:24:24

Hi Khtan,

Thanks for your reply.

i want to show it as two columns as it will have two different result sets

MainGroup SubGroup

Any suggestions or sample please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-23 : 22:32:24

how do you decide which MainGroup and SubGroup to show on the same line ?

Maybe you can post some sample data and the expected result

KH
[spoiler]Time is always against us[/spoiler]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-24 : 12:37:38

sorry for the late reply.

this is how i achieved.

SELECT
	 CASE 
		WHEN ROW_NUMBER() OVER 
		(PARTITION BY GP.GroupName
		 ORDER BY GP.GroupName,GCH.GroupName) = 1 THEN GP.GroupName
		ELSE ''
	 END AS ParentGroup
	,GCH.GroupName
FROM Groups GCH
INNER JOIN Groups GP
ON GCH.IdParentGroup = GP.GroupId;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources