Eqivalent in SQL Server to oracle's "break on" and "skip 1"

Question

Eqivalent in SQL Server to oracle's "break on" and "skip 1"

Lori Nifong

SSC Veteran

Points: 290
More actions
July 21, 2010 at 2:32 pm

#225235

Looking for a formatting equivalent to Oracle's "break on" in in sql server. Here is the SQL and the results. But I want the results to look as in the list below. Any ideas??
use DBA;
go
selectcast(s.database_name as varchar(20)) Database_name,
cast (d.RecoveryMode as varchar(6)) RecoveryMode,
case s.type
when 'L' then 'Trans Log'
when 'D' then 'Database'
else 'No Info' end as Backup_Type,
convert(varchar(20), max(s.backup_finish_date),100) LastBackup_DateTime,
DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days
from msdb.dbo.backupset s
inner join dbo.dba_DBInfo d on d.DatabaseName = s.database_name COLLATE SQL_Latin1_General_CP1_CI_AS
group by database_name, type,d.Recoverymode order by 1,3 desc,2;
Database_name RecoveryMode Backup_Type LastBackup_DateTime Days
-------------------- ------------ ----------- -------------------- -----------
DBA FULL Trans Log Jul 21 2010 12:00PM 0
DBA FULL Database Jul 21 2010 6:00AM 0
master SIMPLE Database Jul 21 2010 6:00AM 0
model FULL Trans Log Jul 21 2010 12:00PM 0
model FULL Database Jul 21 2010 6:00AM 0
LLDDBB FULL Trans Log Jul 21 2010 12:00PM 0
LLDDBB FULL Database Jul 21 2010 6:00AM 0
L45DBB FULL Trans Log Jul 21 2010 12:00PM 0
L45DBB FULL Database Jul 21 2010 6:00AM 0
msdb SIMPLE Database Jul 21 2010 6:00AM 0
Northwind SIMPLE Database Jul 21 2010 6:00AM 0
pubs SIMPLE Database Jul 21 2010 6:00AM 0
testdb FULL Trans Log Jul 21 2010 12:00PM 0
testdb FULL Database Jul 21 2010 6:00AM 0
(10 row(s) affected)
But I want the results to look as in the list below with a break on skip 1 Database_name skip 1:
Database_name RecoveryMode Backup_Type LastBackup_DateTime Days
-------------------- ------------ ----------- -------------------- -----------
DBA FULL Trans Log Jul 21 2010 12:00PM 0
FULL Database Jul 21 2010 6:00AM 0
master SIMPLE Database Jul 21 2010 6:00AM 0
model FULL Trans Log Jul 21 2010 12:00PM 0
FULL Database Jul 21 2010 6:00AM 0
LLDDBB FULL Trans Log Jul 21 2010 12:00PM 0
FULL Database Jul 21 2010 6:00AM 0
L45DBB FULL Trans Log Jul 21 2010 12:00PM 0
FULL Database Jul 21 2010 6:00AM 0
msdb SIMPLE Database Jul 21 2010 6:00AM 0
Northwind SIMPLE Database Jul 21 2010 6:00AM 0
pubs SIMPLE Database Jul 21 2010 6:00AM 0
testdb FULL Trans Log Jul 21 2010 12:00PM 0
FULL Database Jul 21 2010 6:00AM 0
(10 row(s) affected)

Viewing 5 posts - 1 through 4 (of 4 total)

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Lori Nifong (7/21/2010)
Looking for a formatting equivalent to Oracle's "break on" in in sql server. Here is the SQL and the results. But I want the results to look as in the list below. Any ideas??

I do love sqlplus formatting commands too 🙂

In my experience, in the SQL Server world report formatting is done via SS Reporting Services or by whatever front-end is in use.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

I could write the code to do this, but why? Isn't it much easier to use whatever you're using for your user interface? (Honest question ... I don't mind spending some time on it if there is a compelling reason.)

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Lori Nifong SSC Veteran Points: 290 More actions · Answer 3

No frontend to massage or sculpt the data for viewing - this will be dumped into a text file then emailed. As you well know DBA's have many tasks - will have more restful sleep knowing backups are in good shape. I spent a couple hours researching possible formatting options - so assumed it was not an ease code find. The same code must work for 2000, 2005 and 2008. I would not have bothered this forum with my question had I an alternative method of presenting. I’ll continue to seek a solution and remain very open to any suggestions. : ) Many thanks.

LutzM SSC Guru Points: 107049 More actions · Answer 4

Ok, here's what I came up with:

Basically, I add a single row per database using UNION ALL and sort it as required.

The major downside (except for possible performance impacts due to UNION ALL against the same table with a GROUP BY clause): The "number of rows affected" displayed at the end of the file is wrong since it include the blank rows (the exmple below has 5 rows but will display 8 due to the empty row for each database).

-- create and populate test table

SELECT * INTO #yourtable

FROM(

SELECT 'DBA' AS dbname, 'FULL' AS recmod, 'Trans Log' AS filetype UNION ALL

SELECT 'DBA' , 'FULL' ,'Database' UNION ALL

SELECT 'master', 'SIMPLE' ,'Database' UNION ALL

SELECT 'model' , 'FULL' ,'Trans Log' UNION ALL

SELECT 'model' , 'FULL' ,'Database'

) yourtable

SELECT

CASE WHEN recmod='' THEN '' ELSE dbname END AS dbname_,

recmod,

filetype

FROM

(

SELECT dbname,recmod,filetype

FROM #yourtable

UNION ALL

SELECT

dbname,'',''

FROM#yourtable

GROUP BY dbname

) subqry

ORDER BY dbname, CASE WHEN recmod='' THEN 2 ELSE 1 END, recmod

/* result set

dbname_ recmod filetype

------- ------ ---------

DBA FULL Trans Log

DBA FULL Database

master SIMPLE Database

model FULL Trans Log

model FULL Database

(8 row(s) affected)

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]