Dynamic Pivot SQL table [duplicate]

Question

Table dbsize loading with data of every database size every night.

dbsize (id, database_name, database_size, insert_date) 

It looks like this:

 - 1   dbname1   1000   1/1/1900
 - 2   dbname2   2000   1/1/1900 
 - 3   dbname3   3000   1/1/1900 
 - 4   dbname1   1200   1/2/1900 
 - 5   dbname2   2200   1/2/1900

... etc

I need to get result like this

 - insert_date dbname1 dbname2 dbname3
 - 1/1/1900      1000      2000     3000
 - 1/2/1900      1200      2200

Tried with this sql, but nothing:

declare @ColumnNames NVARCHAR(MAX) = ''

declare @SQL NVARCHAR(MAX) = ''

select @ColumnNames += QUOTENAME(database_name) + ',' from  dbo.dbsize


set @ColumnNames = LEFT(@ColumnNames, LEN(@ColumnNames)-1) set @SQL = 
'select * from ( select     database_name   ,insert_date    ,id     from
dbo.dbsize) as BASEDATA

PIVOT   (

    count(id)   for database_name   in      ('

        +@ColumnNames + 

        ')  as PivotTable'


execute sp_executesql @SQL

Answer 1

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

Answer 2

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(d.database_name) 
            FROM DataTable d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT collect_date, ' + @cols + ' from 
            (
                select collect_date
                    , database_size
                    , database_name
                from DataTable
           ) x
            pivot 
            (
                 max(database_size)
                for database_name in (' + @cols + ')
            ) p '


execute(@query)

Final Solution.