pivot error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

pivot error

Author

Topic

naresh0407
Starting Member

30 Posts

Posted - 2010-10-08 : 13:36:51

Hi,

I am using sqlserver 2005
I am gettin error in paasing value.I am trying to check condtion When TotalHours<'24:0' and valu is string so i am enclosing it in string.

How to pass multiple columns to pivot.Please check words in bold.
max(less),max(medium) for Weekstartdate

ALTER PROCEDURE [dbo].[SAR_Sp_GetScheduledHours](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME)
AS
DECLARE @query VARCHAR(MAX)
BEGIN
SET @query = 'SELECT '+ CHAR(39) + 'Forecasted HC as per Hiring Plan' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
'FROM
(SELECT WeekStartDate,
Count(Case When Totalhours<='25:0' Then EmpNo end) as Less,
Count(Case When Totalhours>='26:0' and Totalhours<='48:30' Then EmpNo end) as medium,
Count(Case When Totalhours>='48:30' Then EmpNo end) as greater
FROM TestTable1 group by WeekStartDate)
AS SourceTable
PIVOT
(
max(Less),max(medium)
FOR WeekStartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable'
EXEC(@query)
END

Sachin.Nand

2937 Posts

Posted - 2010-10-08 : 14:24:18

[i]max(Less),max(medium)[\i]

I dont think you can do use multiple aggregate function for pivot clause.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources