Merge multiple rows into one row by replacing NULL in dynamic Pivot query in SQL Server

Last Reply on Feb 22, 2018 12:43 AM By dharmendr

Posted on Feb 21, 2018 11:45 PM

my pivot query result looks like this

21-Feb-18 22-Feb-18 23-Feb-18 24-Feb-18 25-Feb-18 26-Feb-18
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL 21
NULL NULL NULL NULL 12 NULL
NULL NULL NULL 11 NULL NULL
NULL NULL 11 NULL NULL NULL
NULL 11 NULL NULL NULL NULL
11 NULL NULL NULL NULL NULL

and i want it look like below

21-Feb-18 22-Feb-18 23-Feb-18 24-Feb-18 25-Feb-18 26-Feb-18
11 11 11 11 12 21
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(SDate)
FROM (SELECT DISTINCT SDate FROM tblDuty WHERE SDate BETWEEN GETDATE() AND DATEADD(d, 7, GETDATE()) AND StIdNo = 14) AS Weeks

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT ' + @ColumnName + ' 
    FROM tblDuty
    PIVOT (SUM(SessonId )
          FOR SDate IN (' + @ColumnName + ')) AS PVTTable WHERE stidNo = 15 GROUP BY ' + @ColumnName + '' 
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

 

This question does not have replies that have been liked.