Optimal Pivot Query

Question

Post reply

Optimal Pivot Query

mssql_rules

SSCrazy

Points: 2783
More actions
May 5, 2004 at 2:22 pm

#108194

I am looking for an optimal way to pivot some data.
Source Data
====================
database_name type backup_finish_date
===============================================
master  D 2004-05-04 23:41:33.000
model  D 2004-05-02 02:00:16.000
msdb  D 2004-05-02 02:00:20.000
SQLDB  D 2004-05-02 02:00:10.000
model  I 2004-05-05 02:31:51.000
msdb  I 2004-05-05 02:31:49.000
SQLDB  I 2004-05-05 02:31:47.000
SQLDB  L 2004-05-05 06:00:01.000
You can simply create it for yourself by running the query:
SELECT database_name,
type,
MAX(b.backup_finish_date) backup_finish_date
Into    DBBackup
FROM   msdb.dbo.backupset b
WHERE b.type IN ('I', 'L', 'D')
GROUP BY database_name, type
Required Format
=================
DBName  D_Backup  I_Backup  L_Backup
---------------------------------------------------------------------------------------
master  2004-05-04 23:41:33.000 NULL   NULL
model  2004-05-02 02:00:16.000 2004-05-05 02:31:51.000 NULL
msdb  2004-05-02 02:00:20.000 2004-05-05 02:31:49.000 NULL
SQLDB  2004-05-02 02:00:10.000 2004-05-05 02:31:47.000 2004-05-05 06:00:01.000
Here is what I came up with.

SELECT
DBS.database_name,
(SELECT TST2.backup_finish_date FROM DBBackup TST2 WHERE DBS.database_name = TST2.database_name AND TST2.Type = 'D') D_Backup,
(SELECT TST2.backup_finish_date FROM DBBackup TST2 WHERE DBS.database_name = TST2.database_name AND TST2.Type = 'I') I_Backup,
(SELECT TST2.backup_finish_date FROM DBBackup TST2 WHERE DBS.database_name = TST2.database_name AND TST2.Type = 'L') L_Backup
FROM    ( SELECT distinct database_name FROM DBBackup) DBS
Are there any better alternatives ?
thanks.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Peter P SSCrazy Points: 2777 More actions · Answer 1

Hi,

Try this, I think it may be what you're looking for:

SELECT

database_name,

backup_finish_date,

TYPE

FROM DBBackup

GROUP BY database_name,backup_finish_date,TYPE

WITH CUBE

Rockey Nebhwani Grasshopper Points: 15 More actions · Answer 2

Hi Peter,

Can you explain "With CUBE" clause?

I don't have any idea about it...

Plz bear with my ignorance...

Thanks,

Rockey

Victor J Ramos SSC-Addicted Points: 497 More actions · Answer 3

If you have EXCEL 2003, Import the data into EXCEL and then use the Pivot Table and Report functions. You will save yourself a lot of time.

Regards

Peter P SSCrazy Points: 2777 More actions · Answer 4

Actually, I think With CUBE has been deprecated. It's a SQL Server 6.5 feature that allowed for viewing data three dimensionally. Now the prefered method is to use Analysis Services, and Build your CUBE with the Analysis Service Manager. But I thought this might be a quick solution for you.

The previous suggestion of using pivot table in Access or Excel is also a good one.

Good Luck

Pete

Paul McCurdy Ten Centuries Points: 1377 More actions · Answer 5

That is, as long as your expected record size is within Excel's limits. I sometimes drop the data into a SQLServer table and then link to this table from within Excel using External Data Source and an ODBC link on the pivot table. Works real nice.

gdefi SSC-Addicted Points: 493 More actions · Answer 6

This calls for some lateral thinking ...

First off, we require a list of database names in the backup_set;

then we require the last date when differential backups took place;

then we require the last date when log backups took place;

then we require the last date when database backups took place.

So here goes:

SELECT b.database_name, I.I_BACKUP, L.L_BACKUP, D.D_BACKUP

FROM

(SELECT distinct database_name

FROM msdb.dbo.backupset) as B,

(SELECT database_name = left(database_name, 20),

I_BACKUP= MAX(backup_finish_date)

FROM msdb.dbo.backupset

WHERE type = 'I'

group BY Database_Name) as I,

(SELECT database_name = left(database_name, 20),

L_BACKUP= MAX(backup_finish_date)

FROM msdb.dbo.backupset

WHERE type = 'L'

group BY Database_Name) as L,

(SELECT database_name = left(database_name, 20),

D_BACKUP=MAX(backup_finish_date)

FROM msdb.dbo.backupset

WHERE type = 'D'

group BY Database_Name) AS D

WHERE I.database_Name =* B.database_name

and L.Database_Name =* B.Database_Name

and D.Database_Name =* B.Database_Name

NOTE: =* indicates a right outer join.

Result:

database_name I_BACKUP L_BACKUP D_BACKUP

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

deliveries NULL 2004-05-12 21:00:20.000 2004-05-12 21:00:19.000

master NULL NULL 2004-05-12 21:00:05.000

model NULL NULL 2004-05-12 21:00:06.000

msdb NULL NULL 2004-05-12 21:00:08.000

MyAcc NULL NULL 2004-05-12 21:00:09.000

ProManage NULL NULL 2004-05-12 21:00:20.000

PSO NULL NULL 2004-05-12 21:00:24.000

test NULL NULL 2004-05-12 21:00:08.000

(8 row(s) affected)

Sachin Dedhia SSCrazy Points: 2104 More actions · Answer 7

SELECT

b.database_name As DBName,

MAX(CASE b.type WHEN 'D' THEN b.backup_finish_date ELSE NULL END) D_Backup ,

MAX(CASE b.type WHEN 'I' THEN b.backup_finish_date ELSE NULL END) I_Backup ,

MAX(CASE b.type WHEN 'L' THEN b.backup_finish_date ELSE NULL END) L_Backup

FROM msdb.dbo.backupset b

WHERE b.type IN ('I', 'L', 'D')

GROUP BY b.database_name

HTH...

Regards,
Sachin Dedhia

mssql_rules SSCrazy Points: 2783 More actions · Answer 8

Thanks to all.I am choosing Sachin's solution that was better than what I had come up with.

Antares686 SSC Guru Points: 125444 More actions · Answer 9

I have heard nothing on depricating WITH CUBE from the group by syntax and there are no notices on this anywhere I can find where it is being considered.

Peter P SSCrazy Points: 2777 More actions · Answer 10

My Bad. It hasn't been depracated. Something I heard. I shouldn't go around spreading rumors.

Peter P SSCrazy Points: 2777 More actions · Answer 11

My Bad. It hasn't been depracated. Something I heard. I shouldn't go around spreading rumors.