Optimal Pivot Query
-
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.
-
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
-
Hi Peter,
Can you explain "With CUBE" clause?
I don't have any idea about it...
Plz bear with my ignorance...
Thanks,
Rockey
-
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
-
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
-
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.
-
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)
-
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 -
Thanks to all.I am choosing Sachin's solution that was better than what I had come up with.
-
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.
-
My Bad. It hasn't been depracated. Something I heard. I shouldn't go around spreading rumors.
-
My Bad. It hasn't been depracated. Something I heard. I shouldn't go around spreading rumors.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply