error in Pivot

Hi,
I've been trying to get the pivot command to work just so. I've almost got it the way I need it but I'm getting Msg 102, Level 15, State 1, Line 14 Incorrect syntax near ')'.

This is my code:

INSERT INTO dbo.tmpProjExpendFY

SELECT ProjNo, TaskCode, [1] AS P1, [2] AS P2, [3] AS P3, [4] AS P4, [5] AS P5, AS P6,

[7] AS P7, AS P8, [9] AS P9, [10] AS P10, [11] AS P11, [12] AS P12

FROM (SELECT s.ProjNo, tblkpTask.TaskCode

FROM tblProjSched AS s CROSS JOIN

tblkpTask

WHERE (s.DeptCode = 'SWM')) AS dm LEFT OUTER JOIN

(SELECT SUM(e.ActualAmt) AS PYears, e.ProjNo, e.TaskCode

FROM tblActualExpend AS e INNER JOIN

tblBudgetConfig ON e.FiscalYear < tblBudgetConfig.CurrentBudgetYear

GROUP BY e.ProjNo, e.TaskCode) AS dp ON dm.ProjNo = dp.ProjNo AND dm.TaskCode = dp.TaskCode

ORDER BY dm.ProjNo, dm.TaskCode

)p

PIVOT

(

SUM(ActualAmt)

FOR FiscalPeriod IN

( [1], [2], [3], [4], [5], , [7], , [9], [10], [11], [12])

)AS pvt

ORDER BY pvt.ProjNo, pvt.TaskCode;

My end result needs to include all taskcodes for each project regardless of weither it has an expense:

ProjNo TaskCode P1 P2 P3 P4 ect
64BRD PLN 10 23 null 5 ect
there should be 9 total taskcodes per project.

Thansk in advanced for any help.

use the following query..

SELECT
ProjNo
,TaskCode
,[1] AS P1
,[2] AS P2
,[3] AS P3
,[4] AS P4
,[5] AS P5
, AS P6
,[7] AS P7
, AS P8
,[9] AS P9
,[10] AS P10
,[11] AS P11
,[12] AS P12
From
(
Select
dm.ProjNo
,dm.TaskCode
,dp.FiscalPeriod
,dp.ActualAmt ActualAmt
FROM (
SELECT
s.ProjNo
,tblkpTask.TaskCode
FROM tblProjSched AS s
CROSS JOIN tblkpTask tblkpTask
) AS dm
LEFT OUTER JOIN
(
SELECT
e.ActualAmt
,e.ProjNo
,e.TaskCode
,e.FiscalPeriod
FROM
tblActualExpend AS e
) AS dp
ON dm.ProjNo = dp.ProjNo
AND dm.TaskCode = dp.TaskCode

) as Data
PIVOT (SUM(ActualAmt) FOR FiscalPeriod IN ([1], [2], [3], [4], [5], , [7], , [9], [10], [11], [12]))AS pvt
ORDER BY
pvt.ProjNo
,pvt.TaskCode

|||Thanks ManiD that does the trick!

Error in cross tab statement

Hi,

I have a coding which state as below:

CREATE PROCEDURE [dbo].[crossTab]
@.select varchar(8000),
@.sumfunc varchar(100),
@.pivot varchar(100),
@.table varchar(100)
AS

DECLARE @.sql varchar(8000), @.delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE ' + @.pivot + ' Is Not Null')

SELECT @.sql='' , @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN ' + @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)
SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)
SET ANSI_WARNINGS ON
GO

I just woud like why it come out error stated that

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'END'.

when i key in

exec crossTab 'select dbo,fgo,rgt from Table A', 'sum(fgo)', 'rgt', 'Table A'

Please help me

ThanxYet another attempt at a universal cross-tab function...

Does your code error out if you comment out the "EXEC (@.select)" line? If not, then try replacing it with "SELECT @.select" or "PRINT @.select" to see exactly what code is being executed that is causing the error.