Removing mutiple rows in Excel by using the SQL.

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

Removing mutiple rows in Excel by using the SQL.

Author

Topic

Nikhil341
Starting Member

9 Posts

Posted - 2011-12-19 : 12:04:57

Hi,

Can you please clarify me the below question?

We have SQL which is producing output in belo manner.

"WebTransaction" "Total No of Hits" "Calling host""Hits per server"
http://google.com/ 3 A 1
3 B 1
3 C 1
Required out put

"WebTransaction" "Total No of Hits" "Calling host""Hits per server"
http://google.com/ 3 A 1
B 1
C 1
We want to elminate the the "Total No of hits" fromthe seconed row of each transaction.

Thanks in advance
Nikhil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 12:07:15

this is a presentation issue and should be handled in front end. is excel sheet exported from some reporting tool? if yes,best thing would be to handle it at reporting tool

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

Nikhil341
Starting Member

9 Posts

Posted - 2011-12-19 : 12:15:02

I am using SQlPlus to generate a report and directly exporting that data in to a CSV.
Is there any SQl command that we can include in the query to perform my request?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 12:18:59

you can do it in sql but its quite ugly. something like


SELECT WebTransaction,CASE WHEN Rn=1 THEN CAST([Total No of Hits] AS varchar(10)) ELSE '' END AS [Total No of Hits],
[Calling host],[Hits per server]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY WebTransaction,[Total No of Hits] ORDER BY [Calling host]) AS Rn,* 
FROM Table
)t

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

Nikhil341
Starting Member

9 Posts

Posted - 2011-12-19 : 12:21:18

Thanks, will check and let you know

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 12:22:39

wc

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 12:46:02

how would i know

You only knows which table contains the posted details. so replace it with query i gave

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

Nikhil341
Starting Member

9 Posts

Posted - 2011-12-19 : 14:54:36

Hi,

Do we have any other workaround to overcome this issue? (Not using by the table name) because we are retrieving this data from multiple tables.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 22:24:43

quote:
Originally posted by Nikhil341

Hi,

Do we have any other workaround to overcome this issue? (Not using by the table name) because we are retrieving this data from multiple tables.

Thanks

thats not an issue, you just need to add part which generate rownumber to your query and then use that case when in your select. there's no problem even if its a query instead of table

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources