CODEWITHSUNDEEP
sqlsql-server
Marcello Miorelli
Marcello Miorelli

Reputation: 3678

how to get a comma delimited list from 2 columns in a table

there are examples of turning a column into a string list, but I like to believe my case is different.

I am doing a project to monitor disk space growth and from the table below I would like to get something like this:

Drive [Next Autogrowth]                           [DB Files]
F     Database IntEng2 will grow 2 GB in 27 Weeks Data
G     N/A                                         Transaction Log
H     N/A                                         Data, Transaction Log
I     N/A                                         TempDB Data, TempDB Transaction Log

I managed to get the [Next Autogrowth] but I am struggling with the [DB Files] column.

Can you please help?

There are 2 rules to follow:

1) when in the FILE_TYPE column there is ROW 
     A) when in the DATABASENAME column you have "tempdb" - add "TempDB Data" to the [DB Files] ELSE write "Data"

2) when in the FILE_TYPE column there is LOG
     A) when in the DATABASENAME column you have "tempdb" - add "TempDB Transaction Log" to the [DB Files] ELSE write "Transaction Log"

The table is:

SET NOCOUNT ON

DECLARE @T TABLE( 
                DRIVE NCHAR(1)
                ,FILE_TYPE NVARCHAR(20)
                ,DATABASENAME NVARCHAR(100)
                ,AUTOGROWTH_IN_MB INT 
                ,WEEKS_TO_AUTOGROWTH INT 
                ,MONTHS_TO_AUTOGROWTH INT 
            )


INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('F','ROWS','Bocss2',1024,218,49)
INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('F','ROWS','IntEng2',2048,27,7)
INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('H','ROWS','Shop',64,0,0)
INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('G','LOG','Bocss2',2048,147,87)
INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('G','LOG','IntEng2',1000,254,133)
INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('G','LOG','Shop',64,0,0)
INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('H','LOG','Bocss2',2014,147,87)
INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('H','LOG','IntEng2',512,255,133)
INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('I','ROWS','tempdb',1024,2046,614)
INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('I','ROWS','tempdb',1024,2046,682)
INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('I','ROWS','tempdb',1024,2046,613)
INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('I','ROWS','tempdb',1024,2046,682)
INSERT INTO @T (DRIVE,FILE_TYPE,DATABASENAME,AUTOGROWTH_IN_MB,WEEKS_TO_AUTOGROWTH,MONTHS_TO_AUTOGROWTH) VALUES('G','LOG','tempdb',1024,2,1)


SELECT * FROM @T

What I have at the moment is:

;WITH R1 AS (
select
   *
   ,the_closest_event=ROW_NUMBER() OVER (PARTITION BY DRIVE ORDER BY 
                                     CASE WHEN (WEEKS_TO_AUTOGROWTH < CAST(MONTHS_TO_AUTOGROWTH * 4.3 AS INT)) THEN 
                                          WEEKS_TO_AUTOGROWTH ELSE MONTHS_TO_AUTOGROWTH 
                                      END ) 
   ,NextAutoGrowth= CASE WHEN (DATABASENAME = 'tempdb')
                      OR (FILE_TYPE = 'LOG' ) THEN 'N\A' 
                   ELSE 

                     CASE WHEN (WEEKS_TO_AUTOGROWTH < CAST(MONTHS_TO_AUTOGROWTH * 4.3 AS INT)) THEN  --4.3 BECAUSE 1 MONTH = 4.3 WEEKS
                                CASE WHEN (WEEKS_TO_AUTOGROWTH > 0) THEN 
                                 'Database ' + DATABASENAME + ' will grow ' +  

                                   CASE WHEN (AUTOGROWTH_IN_MB >= 1024) THEN -- 1 GB 

                                        CAST(CAST(CAST ( AUTOGROWTH_IN_MB / 1024.00 AS NUMERIC(18,2) )AS REAL) AS VARCHAR) + ' GB in '
                                     ELSE -- use MB
                                        CAST (AUTOGROWTH_IN_MB AS VARCHAR) + ' MB in '
                                   END /*case autogrowth is greater than 1 GB*/

                                  + CAST (WEEKS_TO_AUTOGROWTH AS VARCHAR) + ' Weeks' 
                                  ELSE 'N\A' 

                                END /*case number of weeks > 0*/
                        ELSE
                                CASE WHEN (MONTHS_TO_AUTOGROWTH > 0) THEN 
                                 'Database ' + DATABASENAME + ' will grow ' +  

                                   CASE WHEN (AUTOGROWTH_IN_MB >= 1024) THEN -- 1 GB 

                                        CAST(CAST(CAST ( AUTOGROWTH_IN_MB / 1024.00 AS NUMERIC(18,2) )AS REAL) AS VARCHAR) + ' GB in '
                                     ELSE -- use MB
                                        CAST (AUTOGROWTH_IN_MB AS VARCHAR) + ' MB in '
                                   END /*case autogrowth is greater than 1 GB*/

                                  + CAST (MONTHS_TO_AUTOGROWTH AS VARCHAR) + ' Months' 
                                  ELSE 'N\A' 

                                END /*case number of months > 0 */

                     END /*CASE - weeks or months */

            END /*case when it is tempdb */
 from @T
 )

 SELECT 
 * FROM R1

Upvotes: 1

Views: 85

Answers (2)

dean
dean

Reputation: 10098

Interesting problem :)

;with x as (
select distinct drive, case file_type when 'ROWS' then
            case databasename when 'tempdb' then 'TempDB Data' else 'Data' end
            else 
            case databasename when 'tempdb' then 'TempDB Transaction Log' else 'Transaction Log' end
        end as typ
from @T
),
a as (
    select *, row_number() over(partition by drive order by weeks_to_autogrowth) as rn
    from @t
)
select distinct x.drive, 
case when a.autogrowth_in_mb > 0 and a.weeks_to_autogrowth > 0 then
    'Database ' + a.databasename + ' will grow ' + cast(a.autogrowth_in_mb/1024. as varchar) + ' GB in ' + cast(a.weeks_to_autogrowth as varchar) + ' weeks' 
else 'N/A' end as [Next Autogrowth],
substring(y.list, 1, len(y.list)-1)
from 
x
inner join a on x.drive = a.drive and a.rn = 1
cross apply 
(
    select t2.typ + ', ' as [text()]
    from x t2
    where t2.drive = x.drive
    for xml path('')
) y(list)

Upvotes: 1

Anurag Arya
Anurag Arya

Reputation: 87

Try the following query - may be this helps.

select 
DRIVE Drive,
CASE 
    WHEN FILE_TYPE = 'rows' THEN
    CASE 
        WHEN DATABASENAME = 'tempdb' THEN 'TempDB Data' 
        ELSE 'Data'
    END 
WHEN FILE_TYPE = 'log' THEN 
    CASE
        WHEN DATABASENAME = 'tempdb' THEN 'Data, TempDB Transaction Log' 
        ELSE 'Data, Transaction Log'
    END
ELSE  ''
END 
DBFiles
from 
t

Upvotes: 0

Related Questions