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

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