May 12, 2010 at 2:30 am
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? thnx
May 12, 2010 at 3:05 am
Use a case statement on a row_number function to say that when RowNumber <>1 then show ''
May 12, 2010 at 3:10 am
That kind of formatting should be done in your front end application, not in SQL Server. While SQL can do it, it's not a good place.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2010 at 3:12 am
I think that the recordset should still show the continent and country for each city. The presentation s in the way that you want should be done in the presentation layer and not in the query.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2010 at 4:37 am
I also have a similar scenario, (as suggested) i am trying to do the grouping at front end (SSRS report in my case)
Now the problem, when the results set size increases to around 5 lakh rows reporting services break down. Rendering the data has become a big concern here.
Any suggestion to do the grouping at backend??
Cheers
May 12, 2010 at 5:07 am
Ankur, there are many methods of grouping data , based on the way we want our output to be!
I recommend you go through the following article:
CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]
So please post:
1. Table Structures - CREATE TABLE scripts
2. Constraints or Indexes in the table, - CREATE SCRIPTS
3. SAMPLE DATA - INSERT INTO TABLE scripts
4. Desired output - some visual representation of this.
When u do so, i am sure a lot of us will help u instantly...
IMPORTANT: Create a new thread for your question. It will easy for everyone out here!
Cheers!
May 12, 2010 at 6:23 am
ankur_dba (5/12/2010)
I also have a similar scenario, (as suggested) i am trying to do the grouping at front end (SSRS report in my case)Now the problem, when the results set size increases to around 5 lakh rows reporting services break down. Rendering the data has become a big concern here.
Any suggestion to do the grouping at backend??
Who is even going to look at a report of 500,000 rows ? Thats a lot of trees 🙂
Do you do anything else in your grouping (subtotals ?) , doing this is the SQL wont help you (much) as it will still have to do a lot of work for those.
May 12, 2010 at 9:32 am
Try Below Technique :
CREATE TABLE [dbo].[Area](
[Region] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[City] [varchar](50) NULL
) ON [PRIMARY]
INSERT INTO
([Region],[Country],[City])VALUES('asia','japan','kyoto')
INSERT INTO
([Region],[Country],[City])VALUES('asia','japan','tokyo')
INSERT INTO
([Region],[Country],[City])VALUES('europe','uk','edinburgh')
INSERT INTO
([Region],[Country],[City])VALUES('europe','uk','london')
INSERT INTO
([Region],[Country],[City])VALUES('europe','uk','manchester')
INSERT INTO
([Region],[Country],[City])VALUES('north america','us','chicago')
INSERT INTO
([Region],[Country],[City])VALUES('north america','us','new york')
GO
; WITH CTE ( ID ,Region ,Country , City)
AS ( SELECT CAST(ROW_NUMBER() OVER (ORDER BY COUNTRY ) AS INT) AS ID , CAST(REGION AS VARCHAR) , CAST(COUNTRY AS VARCHAR) , CITY
FROM AREA A
UNION ALL
SELECT CTE.ID , CAST ( '' AS VARCHAR) , CAST ( '' AS VARCHAR), A.CITY FROM AREA A INNER JOIN CTE
ON A.Country = Cte.Country
AND A.Region = Cte.Region
AND A.City < Cte.City
)
SELECT MIN(ID) As ID,Region , Country , Max(City) As City FROM CTE
WHERE Region <> ''
GROUP BY Region , Country
UNION
SELECT MIN(ID),Region , Country , City FROM CTE
WHERE Region = ''
GROUP BY Region , Country , City
Hoe this will help you.
May 13, 2010 at 3:19 am
thanks very much guys. i will have a look at those tips.
have a nice day!
May 13, 2010 at 7:00 am
Try this one out!!!!
Assuming the table name is temp
select isnull(a.continent,''),isnull(a.country,''),city
from (select continent,country,min(city) as city1 from temp group by continent,country) as a
right outer join
temp on a.city1 = temp.city
Hope this can be a solution. Not sure the better one:-)
Prashant Bhatt
Sr Engineer - Application Programming
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply