Grouping - SQL - 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

General SQL Server Forums

New to SQL Server Programming

Grouping - SQL

Author

Topic

ramon6969
Starting Member

11 Posts

Posted - 2010-05-12 : 04:27:36

hi guys is this possible? ive got a table like this:

continent----country----city
europe---------uk-------london
europe---------uk-------manchester
europe---------uk-------edinburgh
asia-----------japan-----tokyo
asia-----------japan-----kyoto
north america--us--------new york
north america--us--------chicago

and want to have a recordset like this.

continent----country----city
europe---------uk-------london
------------------------manchester
------------------------edinburgh
asia-----------japan-----tokyo
-------------------------kyoto
north america--us--------new york
-------------------------chicago

any tips?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-12 : 04:41:26

This is presentation issue! Why can't you try it in front end?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-12 : 05:21:58

Yes senthil suggested you correct as this is presentation issue.
It will be very easy in front end and less effort for the system to work
but still if you want to make it by SQL its possible
try this -


;WITH CTE AS
(
SELECT	Continent, Country, City, 
		ROW_NUMBER() OVER ( Partition BY Continent, Country ORDER BY City ) RowNo
FROM	YourTable
)
SELECT	CASE WHEN RowNo = 1 THEN Continent ELSE '' END AS Continent, 
		CASE WHEN RowNo = 1 THEN Continent ELSE '' END AS Country, City
FROM	CTE

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-12 : 06:23:07

quote:
Originally posted by ramon6969

hi guys is this possible? ive got a table like this:

continent----country----city
europe---------uk-------london
europe---------uk-------manchester
europe---------uk-------edinburgh
asia-----------japan-----tokyo
asia-----------japan-----kyoto
north america--us--------new york
north america--us--------chicago

and want to have a recordset like this.

continent----country----city
europe---------uk-------london
------------------------manchester
------------------------edinburgh
asia-----------japan-----tokyo
-------------------------kyoto
north america--us--------new york
-------------------------chicago

any tips?

Where do you want to show data?
This is known as Suppress if duplicate feature

Madhivanan

Failing to plan is Planning to fail

ramon6969
Starting Member

11 Posts

Posted - 2010-05-13 : 05:17:35

thanks guys, i will have a look at those tips. have a nice day!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:18:59

or hide duplicates if in sql reporting services

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources