SQL Server Pivot Table with multiple column aggregates

Great example on how to pivot multiple columns and aggregates them and then continues by then doing it with a dynamic pivot. Also example is showing off a great tool called SQL Fiddle created by Jake Feasel.

http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates

 

 

Advertisements

SQL Server Dynamic PIVOT Query

Let say you have a table or view that has customers credit scores from every quarter for a few years back and you want to pivot the data out but new data is coming every quarter and you want the query to continue to work.
We need a dynamic pivot query:


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

 --Reporting_Date is the move column.
select @cols = STUFF((SELECT  DISTINCT ',' + QUOTENAME(ED.Reporting_Date)
                    FROM [Database].[dbo].[vw_Data_Reporting] AS ED

            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
		set @query
		--/*This is the Pivot, we are inserting the @cols in two spots.
		 --  One in the Select from the Pivot
		 --  Two in the FOR IN */
      = 'SELECT pvt.[ID_Number],
	  pvt.[Phone_Number],
	  pvt.[SSN],
    ' +    @cols +'
         FROM
    (SELECT [Reporting_Date]
      ,[ID_Number]
      ,[Phone_Number]
      ,[SSN]
      ,[Credit_Score]
	  FROM [Database].[dbo].[vw_Data_Reporting]
	 )
    AS EQ
PIVOT
(
MAX([Credit_Score])
FOR [Reporting_Date]  IN
( '+
@cols +')
) AS pvt
		 '
		 --You have to pick PRINT or Execute but only one.

		-- 1. Uncomment to see the Printed Text for what you are about to run.
		--PRINT @query

		-- 2. Uncomment when you want to run it.
		--execute(@query)

Enjoy and Happy Coding.

Update SQL Jobs to Databases on Netapp Drive

I had to create job to add non naming conversational database names to my existing NetApp snap manager for SQL job.

If a database was not in the LUN I email the users and alerts them to move it.

In this case the databases sit in the R: Drive for .mdf and S: Drive for .ldf

Create SQL Agent Job

then insert Step 1 – Transact-SQL script

USE msdb

GO

SET NOCOUNT ON
 DECLARE @FileName varchar(255)
 DECLARE @File_Exists int
 DECLARE @DBCount int
 DECLARE @DBCounter int
 DECLARE @DBName varchar(50)
 SELECT @DBCount = count(*) from master.dbo.sysdatabases sd
 INNER JOIN sys.master_files mf
 ON mf.database_id = sd.dbid
 WHERE sd.name NOT IN ('master','msdb','model','tempdb')
 AND physical_name NOT LIKE '%.ldf%'
 AND physical_name LIKE '%R:%'
 AND CONVERT(sysname,databasepropertyex(sd.name,'status')) ='ONLINE'
 AND CONVERT(sysname,databasepropertyex(sd.name,'updateability')) ='READ_WRITE'
 --PRINT @DBCount
 BEGIN
 SET @DBCounter = 1
 DECLARE @BackupCmd varchar(500)
 SET @BackupCmd = '"C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe" new-backup -svr ''' + @@SERVERNAME + ''' -d ''' + @@SERVERNAME + ''', ''' + cast(@DBCount as varchar(3)) + ''','
 DECLARE strategy_cursor CURSOR FOR
 SELECT sd.name from master.dbo.sysdatabases sd
 INNER JOIN sys.master_files mf
 ON mf.database_id = sd.dbid
 WHERE sd.name NOT IN ('master','msdb','model','tempdb')
 AND physical_name NOT LIKE '%.ldf%'
 AND physical_name LIKE '%R:%'
 AND CONVERT(sysname,databasepropertyex(sd.name,'status')) ='ONLINE'
 AND CONVERT(sysname,databasepropertyex(sd.name,'updateability')) ='READ_WRITE'
 ORDER BY name
 OPEN strategy_cursor
 FETCH NEXT FROM strategy_cursor INTO @DBName
 WHILE @@FETCH_STATUS = 0
 BEGIN
 IF @DBCounter < @DBCount SET @BackupCmd = @BackupCmd + '''' + @DBName + ''','
 IF @DBCounter = @DBCount SET @BackupCmd = @BackupCmd + '''' + @DBName + ''' -ver –verInst ''DB110'' -mp –mpdir ''C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint'' -RetainBackups 5 -lb -bksif -RetainSnapofSnapInfo 8 -trlog -noutm -gen –mgmt daily'
 SET @DBCounter = @DBCounter + 1
 --PRINT @BackupCmd
 --PRINT @DBCounter
 FETCH NEXT FROM strategy_cursor INTO @DBName
 END
 CLOSE strategy_cursor
 DEALLOCATE strategy_cursor
 END
 --PRINT @BackupCmd
 --DECLARE @rc int
 --EXEC @rc = master.dbo.xp_cmdshell @BackupCmd

EXEC dbo.sp_update_jobstep
 @job_name = N'BACKUP 22:20 DBs',
 @step_id = 1,
 @command =@BackupCmd
 GO

-- Start T-SQL
 USE msdb
 EXEC sp_send_dbmail
 @profile_name = 'DB.sqlserver',
 @recipients = 'jason@test.com',
 @copy_recipients = 'jason@test.com',
 @subject = 'Databases not in NetApp Nightly Snaps',
 @body = 'The Databases below need to be move in R: & S: Drives; if you want them snapped.',
 @execute_query_database = 'msdb',
 @query = 'SELECT sd.name from master.dbo.sysdatabases sd
 INNER JOIN sys.master_files mf
 ON mf.database_id = sd.dbid
 WHERE sd.name NOT IN (''master'',''msdb'',''model'',''tempdb'')
 AND physical_name NOT LIKE ''%.ldf%''
 AND physical_name NOT LIKE ''%R:%'''
 -- End T-SQL --
 GO