CODEWITHSUNDEEP
t-sqlpivot
Kevin Bain
Kevin Bain

Reputation: 23

Duplicate Columns in TSQL Pivot

I have a set of raw data stored in a database that looks like the following:

ReportDate  Server  AvgAvail    outageID
6/4/2015    CPU1    0.9797  11111111
6/4/2015    CPU2    0.9999  NULL
6/4/2015    CPU3    0.9999  NULL
6/4/2015    CPU4    0.9999  NULL
6/5/2015    CPU1    0.9999  NULL
6/5/2015    CPU2    0.9999  NULL
6/5/2015    CPU3    0.9887  22222222
6/5/2015    CPU4    0.9999  NULL
6/6/2015    CPU1    0.9999  NULL
6/6/2015    CPU2    0.9999  NULL
6/6/2015    CPU3    0.9999  NULL
6/6/2015    CPU4    0.9999  NULL
6/6/2015    CPU5    0.9999  NULL

I currently use TSQL with dynamic SQL to generate a table because the report dates can be chosen via a date picker. I would like to get the output to look like the following:

Server  OutageID    6/4/2015    OutageID    6/5/2015    OutageID    6/6/2015
CPU1    11111111    0.9797      NULL        0.9999      NULL        0.9999
CPU2    NULL        0.9999      22222222    0.9887      NULL        0.9999
CPU3    NULL        0.9999      NULL        0.9999      NULL        0.9999
CPU4    NULL        0.9999      NULL        0.9999      NULL        0.9999
CPU5    NULL        NULL        NULL        NULL        NULL        0.9999

Here is my current SQL:

DECLARE @cols NVARCHAR(2000) 
DECLARE @cols2 NVARCHAR(2000) 
DECLARE @query NVARCHAR(4000) 

SELECT @cols = Coalesce( @Cols+',['+ ReportDate +']','['+ ReportDate+']'), 
@cols2 = Coalesce( @Cols2+',[outageID],['+ ReportDate +']','[outageID],['+ ReportDate+']') 
FROM AVTABLE WHERE ReportDate > getdate() - 22 GROUP BY ReportDate ORDER BY ReportDate 

SET @query = 'SELECT Server,'+@cols2+' FROM AVTABLE AS SRC PIVOT (Min(AvgAvail) FOR ReportDate in ('+@cols+')) AS PVT' 

EXEC(@query)

Problem is that the Pivot is duplicating rows with outage numbers. Any thoughts on how to fix it? TIA

Upvotes: 1

Views: 59

Answers (1)

ClearLogic
ClearLogic

Reputation: 3682

This query will give you the results you want.does not use pivot syntax.

DECLARE @cols NVARCHAR(2000) =''
DECLARE @cols2 NVARCHAR(2000)=''
DECLARE @query NVARCHAR(4000)='' 

SELECT @cols = @cols + ',[OutageID] =  MAX(CASE  WHEN ReportDate = '''+CAST(ReportDate as varchar)+''' THEN   OutageID  END)'
                     + ',['+CAST(ReportDate as varchar)+'] = MAX(CASE  WHEN ReportDate ='''+CAST(ReportDate as varchar)+''' THEN   avgavail  END)'
FROM AVTABLE  GROUP BY ReportDate ORDER BY ReportDate 

SET @query = 'SELECT [server] ' +@cols+ ' FROM AVTABLE GROUP BY [server] '

EXECUTE  (@query)

SQLFiddle for Query

Upvotes: 1

Related Questions