ALTER PROCEDURE [dbo].[MonthlySales] 
-- Add the parameters for the stored procedure here
	@YEAR int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT  @listCol = STUFF(( SELECT DISTINCT

                                '],[' + ltrim(str(Month(inv_dt)))

                        FROM    oehdrhst_sql

                        ORDER BY '],[' + ltrim(str(Month(inv_dt)))

                        FOR XML PATH('')

                                    ), 1, 2, '') + ']'
SET @query =
'
Declare @YEAR int


SELECT * FROM

      (SELECT cus_no,slspsn_no, Month(inv_dt) OrderYear, tot_sls_amt

            FROM oehdrhst_sql
          where year(inv_dt)=@YEAR

            ) src

PIVOT (SUM(tot_sls_amt) FOR OrderYear

IN ('+@listCol+')) AS pvt'

 

EXECUTE (@query)
END

SET @query =
'
Declare @YEAR int


SELECT * FROM

      (SELECT cus_no,slspsn_no, Month(inv_dt) OrderYear, tot_sls_amt

            FROM oehdrhst_sql
          where year(inv_dt)='  + CAST(@YEAR varchar(4)) +  '

            ) src

PIVOT (SUM(tot_sls_amt) FOR OrderYear

IN ('+@listCol+')) AS pvt'