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

Last Reply one year ago By dharmendr

Posted one year ago

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

 

You are viewing reply posted by: dharmendr one year ago.
Posted one year ago

Hi satabeach,

Check this example. Now please take its reference and correct your code.

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

Query

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @ColumnNameWithSum AS NVARCHAR(MAX)
 
--Get distinct values of the PIVOT Column
SELECT @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME(Country) FROM (SELECT DISTINCT Country FROM Customers) as t

SELECT @ColumnNameWithSum = ISNULL(@ColumnNameWithSum + ',','') + 'SUM('+QUOTENAME(Country)+') as '+ QUOTENAME(Country) FROM (SELECT DISTINCT Country FROM Customers) as t

--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
  N'SELECT ' + @ColumnNameWithSum + '
    FROM Customers
    PIVOT (SUM(CustomerId)
          FOR Country IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

Output

France India Russia United States
3 2 4 1