Stored Procedure-Pivote Table

Question

Hi All,

  I created one stored Procedure for dynamicaly add the Columns below.


-- ============================================= 
-- Author:  Gokilavasan
-- Create date: 28/07/2012 
-- Description: VAN Stock Report 
-- ============================================= 
 
-- ============================================= 
 
CREATE PROCEDURE [dbo].[sp_Report_TESTVanstock] 
 
 @StartDate DATETIME, 
 @EndDate DATETIME 
 
AS 
 Declare @ItemCode varchar(50), 
@Technician varchar(50), 
@Quantity Decimal 
 
BEGIN 
 -- SET NOCOUNT ON added to prevent extra result sets from 
 SET NOCOUNT ON; 
 
DECLARE @ColourColumn varchar(MAX) 
DECLARE @ColourColumn1 varchar(MAX) 
DECLARE @sql varchar(max) 
 
CREATE TABLE #tblReportResult 
( 
ItemCode varchar(50), 
Technician varchar(50), 
Quantity Decimal 
) 
 
Insert into  #tblReportResult (Technician,ItemCode,Quantity) 
Select Distinct 
Rtrim(Replace(EM.FirstName + '' + ISNULL(EM.MiddleName,'') + '' + EM.LastName, ' ', ' ')) TechnicianName , 
ST.ItemCode, 
ST.Quantity 
FROM EmployeeMaster EM, 
Stockintechnician ST 
Where 
ST.Technician=EM.EmployeeID 
and EM.Designation in('Technician','Driver') 
--and ST.Technician <>'' 
 
order by ST.ItemCode 
 
-- Creating Column Names for Pivot 
SELECT @ColourColumn = COALESCE(@ColourColumn+ ',', '') + 
'['+Rtrim(Replace(FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName, '  ', ' '))  +']' 
FROM EmployeeMaster 
where Designation in('Technician','Driver') 
 
SELECT @ColourColumn1 = COALESCE(@ColourColumn1+ ',', '') + 
'isnull(['+Rtrim(Replace(FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName, '  ', ' '))  +'],0) as '+Rtrim(Replace(FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName, '  ', ' '))  +'' 
FROM EmployeeMaster 
where Designation in('Technician','Driver') 
 
 
 
print @ColourColumn1 
 
--DROP TABLE #tblReportResult 
 
SET @sql = 
' 
SELECT 
* 
FROM 
( 
SELECT 
ItemCode, 
Technician, 
Quantity 
FROM #tblReportResult 
 
 
 
 
) AS P 
PIVOT 
( 
sum(Quantity)for Technician  IN ('+@ColourColumn+') 
) AS pv 
 
' 
 
EXEC (@sql) 
 
END 
 
-- exec sp_Report_TESTVanstock '5/01/2012','9/30/2012' 

Dynamic Pivot table, how to remove NULL values without knowing column names?

when I execute the stored procedure I got the values.but the values not in cells are displayed as NULL value.i need to remove the NULL values and insert the '-'




I tried a lot.but i did not get the solution



Please Help me










Thanks & Regards,

Gokilavasan.M