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'
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