Dynamic Pivot SQL table
You were so close. Only a missing parenthesis at the very end of the dynamic sql string, the finishing parenthesis for the PIVOT.

If you need to be able to handle multiple rows in dbsize with same database_name, you have to add a COUNT column and GROUP BY in your basedata query and change from COUNT(id) to SUM(count_column) in your PIVOT.

DECLARE @ColumnNames nvarchar(MAX) = ''
DECLARE @SQL nvarchar(MAX) = ''

SELECT @ColumnNames += QUOTENAME( database_name) + ',' from (select distinct database_name from dbo.dbsize) t
SET @ColumnNames = LEFT(@ColumnNames, LEN(@ColumnNames)-1) 
SET @SQL = 'SELECT * FROM ( SELECT database_name, COUNT(*) AS cnt FROM dbsize GROUP BY database_name) AS basedata
            PIVOT ( SUM(cnt) FOR database_name IN (' +@ColumnNames + ')) AS PivotTable'
EXECUTE sp_executesql @SQL


SQL Fiddle