Preparing a query to paste into excel to get growth by month I have a table that looks like this:

|DB   | DATA_MB | LOG_MB   |                         |
| DB1 | 4,4375  | 2,25     | 2019-08-06 10:37:49.160 |   |
| DB2 | 8       | 0,5      | 2019-08-06 10:37:49.160 |   |
| DB3 | 3,1875  | 0,75     | 2019-08-06 10:37:49.160 |   |
| DB4 | 20,8125 | 26,1875  | 2019-08-06 10:37:49.160 |   |
| DB5 | 5,1875  | 10,125   | 2019-08-06 10:37:49.160 |   |
| DB6 | 4,1875  | 1,0625   | 2019-08-06 10:37:49.160 |   |
| DB7 | 1971    | 1952,375 | 2019-08-06 10:37:49.160 |   |
| DB8 | 12      | 2,5      | 2019-08-06 10:37:49.160 |   |


it repeats for all database, every day  ( its a job running ).

The I did this to use it on excel to create those cool graphics:

;WITH SQL AS
    (SELECT name, 
            [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
     FROM
        (SELECT NAME,
            MONTH(DATE) AS 'MES_DATA',
            AVG(DATA_SIZE_MB) AS 'SIZE_MB'
         FROM TblDataBaseSize
            GROUP BY NAME,MONTH(DATE)  
    ) P
PIVOT
    (AVG(SIZE_MB)
        FOR
            MES_DATA IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))
    PVT)
SELECT * FROM SQL
ORDER BY 1


Now I have this ( I will fix numbers as months later, I hate pivot as I really don't understand then, so I just managed to create it ).

| name | 1    | 2    | 3    | 4    | 5    | 6   | 7   | 8               | 9                | 10   | 11   | 12   |
 -----|------|------|------|------|------|------|------|------------------|------------------|------|------|------|
| DB1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 3,1875           | 3,1875           | NULL | NULL | NULL |
| DB2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1971             | 1971             | NULL | NULL | NULL |
| DB3 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 3,1875           | NULL             | NULL | NULL | NULL |
| DB4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 32891,5769230769 | 33600,5          | NULL | NULL | NULL |
| DB5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 39027,1875       | 60598,8541666667 | NULL | NULL | NULL |
| DB6 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 12               | 12               | NULL | NULL | NULL |
| DB7 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 619,293269230769 | 667,0625         | NULL | NULL | NULL |
| DB8 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 4                | 4                | NULL | NULL | NULL |


But I noticed that I need something like this, for excel to understand what I really want:

|   | DB1 | DB2 | DB3 | DB4 |
|---|-----|-----|-----|-----|
| 1 | 10  | 1   | 2   | 60  |
| 2 | 12  | 2   | 2   | 65  |
| 3 | 12  | 3   | 2   | 70  |


then i can create a cool "line graphic".


Is there a way to pivot a query to looks like that?

Table looks like this:

CREATE TABLE [dbo].[TblDataBaseSize](
    [name] [varchar](500) NULL,
    [Data_Size_MB] [real] NULL,
    [Log_Size_MB] [real] NULL,
    [Date] [datetime] NULL
) ON [PRIMARY]
Well I could crate a dynamic pivot. Maybe it can help you guys with your own tasks someday:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(name) 
                    from TblDataBaseSize
                    group by name
                    order by name
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT date,' + @cols + ' from 
             (
                select month(date) as date, name, convert(int,data_size_mb) as data_size_mb
                from TblDataBaseSize
            ) x
            pivot 
            (
                avg(data_size_mb)
                for name in (' + @cols + ')
            ) p '

execute(@query);


The reason that i needed to create the dynamic is because I dont know how many databases I will have. This one above will create for a fixed numbers of databases ( or something different ).

select *
from 
(
  select name, month(date) as mes, data_size_mb
  from TblDataBaseSize where name not in ('master','model','msdb','tempdb')
) src
pivot
(
  avg(data_size_mb)
  for name in (
[DB1], [DB2]    ,[DB3]  ,[DB4]  ,[DB5] ...
)

) piv;


I must say it didn't help me with anything because excel still cant understand what I need haha but it was a cool challenge.