1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

CREATE TABLE #results (Site NVARCHAR(250),
                       VoteTypeId TINYINT,
                       NumberOfVotes INT);
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + IIF(LEN(@sql) > 0, 'UNION ALL', 'INSERT INTO #results') + N'
SELECT ''' + name + ''', v.VoteTypeId, COUNT(*)
  FROM ' + QUOTENAME(name) + '.dbo.Votes AS v
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Posts AS p
    ON p.Id = v.PostId
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Users AS u
    ON u.Id = p.OwnerUserId
  WHERE u.AccountId = ' + CAST(##AccountId:int## AS NVARCHAR) + '
  GROUP BY v.VoteTypeId
'
FROM sys.databases WHERE database_id > 5;
-- AccountId: The global account ID of the user, which you can find in the URL of their network profile. Visit this link: http://stackexchange.com/users/current to see your own ID.

EXEC (@sql);

-- Total per site
DECLARE @Total TINYINT = 255;
INSERT INTO #results
SELECT Site, @Total, SUM(NumberOfVotes)
  FROM #results
  GROUP BY Site;
-- Grand total
INSERT INTO #results
SELECT 'Total', VoteTypeId, SUM(NumberOfVotes)
  FROM #results
  GROUP BY VoteTypeId;

-- Site details, cf. https://meta.stackexchange.com/a/359795/295232
CREATE TABLE #Sites (URL NVARCHAR(100), SiteName NVARCHAR(100), DatabaseName NVARCHAR(50));
-- fetch Markdown for post containing table with site details
DECLARE @md NVARCHAR(MAX);
SELECT TOP 1 @md = Text
  FROM [StackExchange.Meta].dbo.PostHistory
  WHERE PostId = 359796 AND PostHistoryTypeId = 5
  ORDER BY CreationDate DESC;
-- split Markdown on pipe symbol
CREATE TABLE #TableEntries (ID INT IDENTITY(1, 1), Value NVARCHAR(100));
INSERT INTO #TableEntries (Value)
SELECT LEFT(TRIM(VALUE), 100) FROM STRING_SPLIT(@md, '|')
  WHERE LEN(TRIM(REPLACE(REPLACE(VALUE, CHAR(10), ''), CHAR(13), ''))) > 0;
-- 'pivot' - we have to discard the introductory text, headers and horizontal lines
INSERT INTO #Sites
SELECT t0.Value, t1.Value, t2.Value
  FROM #TableEntries AS t0
  INNER JOIN #TableEntries AS t1
    ON t0.Id + 1 = t1.Id
  INNER JOIN #TableEntries AS t2
    ON t0.Id + 2 = t2.Id
  WHERE t0.Id >= 8 -- that's where the real content starts
    AND t0.Id % 3 = 2;

-- total
INSERT INTO #Sites
VALUES('https://stackexchange.com', 'Grand total', 'Total');

-- pivot
DECLARE @columns NVARCHAR(MAX) = '', @columns_ NVARCHAR(MAX) = '';
WITH vt AS (SELECT v.Id, v.Name FROM #results AS r
          INNER JOIN VoteTypes AS v ON v.Id = r.VoteTypeId
          GROUP BY v.Id, v.Name)
SELECT @columns = 
  STRING_AGG(QUOTENAME(Id) + ' AS ' + QUOTENAME(Name), ', ')
    WITHIN GROUP (ORDER BY Id),
  @columns_ =
  STRING_AGG(QUOTENAME(Id), ', ')
    WITHIN GROUP (ORDER BY Id)
  FROM vt;
SET @sql = 'SELECT SiteName, ' + QUOTENAME(@Total) + ' AS Total, '
  + @columns + '
  FROM (SELECT SiteName, VoteTypeId, NumberOfVotes
          FROM #results AS r
          INNER JOIN #Sites AS s
          ON r.Site = s.DatabaseName) AS source
  PIVOT (
    AVG(NumberOfVotes)
    FOR VoteTypeId IN (' + QUOTENAME(@Total) + ', ' + @columns_ + ')
  ) AS pvt
  ORDER BY 2 DESC -- Total';
EXEC (@sql);
     
DROP TABLE #results;

fork query

edited apr 1 21

created apr 1 21 Glorfindel