En liten PIVOT

Hej på er,

Nu när det gått en vecka tänkte jag passa på att presentera mig. Mattias Lind heter jag och började den 1:a augusti här på SQL Service. De senaste tio åren har jag kört som egenföretagare, min profil som talare, instruktör, mentor och konsult gäller fortfarande, dock numera som en i ett fantasktiskt team här hos SQL Service.

Sedan tänkte jag också dela med mig lite av mina erfarenheter i den här bloggen, min personliga blog http://blog.mssqlserver.se lever givetvis kvar även fast också jag skriver här med jämna mellanrum. Till att börja med så tänkte jag visa er hur PIVOT kan fungera i verkligheten på ett lite mer dynamiskt sätt. Ofta när jag håller kurser får jag frågan hur jag skulle ha gjort, här kommer jag att skriva inlägg som svar på de frågorna.

Men innan vi börjar behöver vi lite data. Jag tänkte att resultatet skulle vara en pivottabell över våran databas server, där varje databas får en rad och typen av databasobjekt blir kolumner och antalet av de här objekten blir en summa. Med de förutsättningarna skapar vi först en tabell som en variabel, därefter fyller vi den tabellen med metadata med hjälp av en cursor.

— Först skapar vi en tabellvariabel samt deklarerar lite variabler vi behöver
DECLARE @DatabaseObjects TABLE
  (
    DatabaseName sysname,
    ObjectType sysname,
    ObjectName sysname
  );
DECLARE
  @DatabaseName sysname,
  @SQLStatement varchar(max),
  @ExecuteStatement varchar(max),
  @Columns varchar(max);

— Här börjar laddningen av simulerat data
SET NOCOUNT ON;
SET @SQLStatement = ‘SELECT ”{DatabaseName}” AS [DatabaseName], [type_desc] AS [ObjectType], [name] AS [ObjectName] FROM [{DatabaseName}].[sys].[objects];’;
DECLARE
  DatabaseCrawler CURSOR
  FOR SELECT Name FROM sys.Databases ORDER BY Name ASC;
OPEN DatabaseCrawler;
FETCH NEXT FROM DatabaseCrawler INTO @DatabaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @ExecuteStatement = REPLACE(@SQLStatement, ‘{DatabaseName}’, @DatabaseName);
  INSERT INTO @DatabaseObjects
  EXEC (@ExecuteStatement);
  FETCH NEXT FROM DatabaseCrawler INTO @DatabaseName;
END;
CLOSE DatabaseCrawler;
DEALLOCATE DatabaseCrawler;
SET NOCOUNT OFF;

— Först skapar jag en temporär tabell eftersom tabellvariabeln inte kan nås i kommande EXEC-uttryck
SELECT * INTO #DatabaseObjects FROM @DatabaseObjects

— Sedan populerar jag en variabel med en kommaseparerad lista av unika objektstyper
SELECT @Columns = ISNULL(@Columns, ”) + ‘[‘ + [Column] + ‘],’ FROM (SELECT DISTINCT ObjectType AS [Column] FROM @DatabaseObjects) Columns

— Därefter tar jag bort det avslutande kommatecknet i listan
SET @Columns = LEFT(@Columns, LEN(@Columns) – 1)

— SELECT-uttrycket med PIVOT-klausulen, {Columns} är en platshållare. Den återkommer två gånger.
SET @SQLStatement = ‘SELECT DatabaseName, {Columns} FROM #DatabaseObjects PIVOT (Count(ObjectName) FOR ObjectType IN ({Columns})) AS pvt’

— Ersätter platshållaren med innehållet i listan.
SET @ExecuteStatement = REPLACE(@SQLStatement, ‘{Columns}’, @Columns);

— Slutligen kör jag SQL-uttrycket och tar bort den temporära tabellen
EXEC (@ExecuteStatement)
DROP TABLE #DatabaseObjects

Så varför all denna kod för att få ut ett pivoterat resultat. Titta på innehållet i variabeln @SQLStatement.
    SELECT DatabaseName, {Columns} FROM #DatabaseObjects
    PIVOT (Count(ObjectName) FOR ObjectType IN ({Columns})) AS pvt
{Columns} är listan över kolumner, eftersom dessa hämtas från tabellen @DatabaseObjects och dess kolumn ObjectTypes.Om jag inte använt det här skriptet hade jag således varit tvungen att skriva in kolumnnamnen manuellt. Nu använder jag istället REPLACE-funktionen för att ersätta {Columns} med en kommaseparerad lista i variabeln @Columns.