Need a Line Seperator Between Data

Question

Post reply

Need a Line Seperator Between Data

JC-3113

SSCrazy Eights

Points: 8371
More actions
August 11, 2009 at 10:04 am

#137242

Hi Folks
I am trying toi find a way to add a blank line between like data.
Is there a way in T-SQL?
Example code:
use MASTER;
go
select
logdate,
CAST (databaseName as varchar(24)) databasename,
CAST (ROUND (logsize,2,0) AS DECIMAL (18,2)) logsize_mb,
CAST (ROUND (logused,2,0) AS DECIMAL (18,2)) logused_pct,
CAST (ROUND (100-logused,2,0) AS DECIMAL (18,2)) logfree_pct
from
dbo.logSpaceStats
order by
databaseName,
logdate;
go
Example output (partial):
logdate databasename logsize_mb logused_pct logfree_pct
----------------------- -------------------- ------------ -------------- --------------
2009-08-10 04:00:05.393 AdventureWorks 17.99 60.23 39.77
2009-08-11 04:00:05.170 AdventureWorks 17.99 60.23 39.77
2009-08-10 04:00:05.393 AdventureWorksDW 1.99 64.51 35.49
2009-08-11 04:00:05.170 AdventureWorksDW 1.99 64.51 35.49
2009-08-10 04:00:05.393 AdventureWorksLT 1.99 45.32 54.68
2009-08-11 04:00:05.170 AdventureWorksLT 1.99 45.32 54.68
I would like to be able to generate a blank line when the databasename changes so I can get some seperation
Example:
logdate databasename logsize_mb logused_pct logfree_pct
----------------------- -------------------- ------------ -------------- --------------
2009-08-10 04:00:05.393 AdventureWorks 17.99 60.23 39.77
2009-08-11 04:00:05.170 AdventureWorks 17.99 60.23 39.77
2009-08-10 04:00:05.393 AdventureWorksDW 1.99 64.51 35.49
2009-08-11 04:00:05.170 AdventureWorksDW 1.99 64.51 35.49
2009-08-10 04:00:05.393 AdventureWorksLT 1.99 45.32 54.68
2009-08-11 04:00:05.170 AdventureWorksLT 1.99 45.32 54.68
Thanks
Jim

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

You shouldn't be doing formatting in T-SQL. You should be using a formatting tool like SSRS.

You can trick T-SQL into doing this by adding a COMPUTE BY clause (which will also require an ORDER BY clause).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

drew.allen (8/11/2009)
You shouldn't be doing formatting in T-SQL. You should be using a formatting tool like SSRS.
You can trick T-SQL into doing this by adding a COMPUTE BY clause (which will also require an ORDER BY clause).
Drew

So... you would have the DBA take the extra time to write an SSRS report just to make a very simple DBA tool skip a line? While I agree that such things for production software with a GUI should be done on the GUI side of the code, I'd recommend that doesn't apply here and I'd rather have the DBA doing something more productive than writting an SSRS report when the simple insertion of a blank line will do here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 3

Thanks Drew

but i guess we differ in opinion as I have been writiung Oracle SQL for many years and I do not quite understand whay i should not be able to write my own sql for sql server using sqlcmd command line/t-sql.

Is there any basis for your response on this

or is this your preference ?

Just curious

Thanks

Jim

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

JC (8/11/2009)
Thanks Drew
but i guess we differ in opinion as I have been writiung Oracle SQL for many years and I do not quite understand whay i should not be able to write my own sql for sql server using sqlcmd command line/t-sql.
Is there any basis for your response on this
or is this your preference ?
Just curious
Thanks
Jim

Actually, there is a basis for his response but it's a response for GUI developers and not DBA's. The problem is that a lot of people tax SQL Server with silly formatting that should be done on the GUI side of the house. Why should it be done on the GUI side of the house? Most importantly is because things like the format of dates, currency, and decimal numbers vary greatly from country to country. If you format the that stuff on the SQL Server side, then you have to jump through hoops to reformat it correctly for a given country on the GUI side. If you just send it as data and let formatting occur using the local settings of the client machined, then everybody is happy and you've relieved SQL Server from having to spend any clock cycles doing an formatting.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Ok... although a nifty trick, the problem with using something like COMPUTE BY for this is that it returns multiple result sets. I can see the need for this being a single result set. With that in mind, the following code should do the trick both in the grid mode and the text mode...

SELECT d.LogDate,

CASE WHEN d.X = 0 THEN '' ELSE d.DatabaseName END AS DatabaseName,

d.LogSize_MB,

d.LogUsed_PCT,

d.LogFree_PCT

FROM (

SELECT ROW_NUMBER() OVER (PARTITION BY DataBaseName ORDER BY LogDate, DatabaseName) AS X,

CAST(logdate AS CHAR(26)) AS LogDate,

CAST(databaseName as varchar(24)) databasename,

STR(logsize,12,2) logsize_mb,

STR(logused,12,2) logused_pct,

STR(100-logused,12,2) logfree_pct

FROM dbo.logSpaceStats

UNION ALL

SELECT DISTINCT 0,'',CAST (databaseName as varchar(24)) databasename,'','',''

FROM dbo.logSpaceStats

)d

ORDER BY d.DatabaseName, d.x

;

Jim... to get better and faster answers in the future, you might want to change the format of your original post. See the first link in my signature line for what I'm talking about.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76985 More actions · Answer 6

There's actually another reason. SQL returns (a) result set(s). The blank line is simply not part of the result set, so your options are to separate your single result set into multiple result sets just to get some kind of break between them and/or to put a carriage return/line feed that will only show in some interfaces. (The carriage return/line feed will show up in the text view of query analyzer, but not the grid view, for example.)

If you really need to do this, here is another approach.

WITH logs AS (

SELECT

Row_Number() Over(ORDER BY databasename) AS OverallRow

, Row_Number() OVER( PARTITION BY databasename ORDER BY databasename, logdate) AS DBRow

, databasename

, logdate

FROM dbo.LogSpaceStats

)

SELECT CASE WHEN DBRow = 1 AND OverallRow > 1 THEN Char(13) + Char(10)+ ' ' ELSE '' END AS BlankLine, logs.*

FROM logs

You could probably make this simpler by sorting the logdate in DESC order in the CTE, but ASC order in the main query (or vice versa). This would solve some of the problems you will run into with an extra column at the beginning, particularly since the first real column contains a date.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

The only problem with that is, stuff doesn't line up when you print action characters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 8

Thanks Jeff

i will look at the document

I need all the help i can get at this point with sql server

Thanks

jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 9

Thanks Drew

appreciate the input

you guys know alot more than i do

Jim