2

This question already has an answer here:

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

marked as duplicate by Paul White Sep 27 '15 at 1:19

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • What do you mean by "but nothing"? – Colin 't Hart Sep 11 '15 at 9:47
  • I get some errors, I think that my code is not working at all. – Darko Milic Sep 11 '15 at 10:05
0

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

  • Great, now it working, but now I get next error message: The column 'database_name1' was specified multiple times for 'PivotTable'. – Darko Milic Sep 11 '15 at 9:55
  • Your solution assumes unique values for database_name in table dbsize. The error message indicates there are multiple rows with database_name= 'database_name1'. – larsis Sep 11 '15 at 10:00
  • Updated the answer and the fiddle to handle multiple rows in dbsize with same database_name. – larsis Sep 11 '15 at 10:07
  • This is working. But, its not what I need. I need to get 1 row per date. That Row will contain every database_size. – Darko Milic Sep 11 '15 at 13:22
0
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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.