0

I have data as shown below:

server_name     database_name   totaldb_size_megs   MonthYear
NAULNSQD003 DBAInfo 18  Aug-18
NAUSPLNSQD003   master  6.4375  Aug-18
NALNSQD003  model   33  Sep-18
NAUSPQD003  msdb    15.6875 Aug-18
NAUSSQD003  tempdb  824 Aug-18
NAUQD003    DBAInfo 18  Sep-18

How to achieve it in below format. Columns and rows:

Servername  Dataabse name    Aug    Sep     Growth 
Servername    Dbname        Dbsize  Dbsize  Aug-Sep

I have tried pivot but unable to get it

select MODIFDT,OldItemNumber from
(
  select  MODIFDT,OldItemNumber,rowid,ShortDescription
  from cdvtool..itemmaster

) d
pivot
(
  max(rowid)
  for ShortDescription in (MODIFDT,OldItemNumber)
) piv

And have a logic of: If I query it for current month(March), i need to get the details of the previous two months data

Seetha
  • 11
  • 1

1 Answers1

1

As shown in the linked question, this is just pivoting and conditional aggregation:

SELECT ServerName,
       DatabaseName,
       MAX(CASE WHEN MonthYear = '20180801' THEN TotalDBSize END) AS AugSize, --Used a date, as not sure what data type your "MonthYear" really is
       MAX(CASE WHEN MonthYear = '20180901' THEN TotalDBSize END) AS SepSize, --Used a date, as not sure what data type your "MonthYear" really is
       MAX(CASE WHEN MonthYear = '20180801' THEN TotalDBSize END) - MAX(CASE WHEN MonthYear = '20180901' THEN TotalDBSize END) AS Growth
FROM dbo.YourTable
GROUP BY ServerName,
         DatabaseName;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Thank you, but how to handle the month and year as they were dynamic. – Seetha Mar 11 '21 at 09:19
  • Parametrise them instead, @Seetha . Though that won't change the names of the columns; but that wasn't part of the scope of your question. See [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) on how to make it dynamic. – Thom A Mar 11 '21 at 09:24
  • 2
    Honestly, however, I don't suggest going down the dynamic SQL route if you don't understand how to pivot in the first place; I would suggest perhaps it time to do this in your presentation layer, not the RDBMS. – Thom A Mar 11 '21 at 09:26