COALESCE result Error : is not a valid identifier.

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

SQL Server 2008 Forums

Transact-SQL (2008)

COALESCE result Error : is not a valid identifier.

Author

Topic

Rajana
Starting Member

2 Posts

Posted - 2012-07-27 : 05:46:58

I am trying to take Attendance sheet in pivot format.
Here is my entire query. and error message. Whats wrong in this.
Thanks in advance.
------------------------
DECLARE @sql AS varchar(max)
DECLARE @pivot_list AS varchar(max)
DECLARE @select_list AS varchar(max)

SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + CONVERT(varchar, DayNum) + ']',
@select_list = COALESCE(@select_list + ', ', '') + '[' + CONVERT(varchar, DayNum) + '] AS [' + CONVERT(varchar, DayNum) + ']'
FROM
(
SELECT DISTINCT DayNum
FROM
(
select DATEPART(DAY, CAST(CONVERT(VARCHAR,swipe_datetime,101) as date)) AS DayNum, '1' as Present,A.emp_CDSID
from CDSI_DB.dbo.EmpSwipe as A
where A.emp_CDSID in (select emp_CDSID from CDSI_DB.dbo.Employee)
AND
CAST(CONVERT(VARCHAR,A.swipe_datetime,101) as datetime) between '04/01/2012' and '04/10/2012'
) as EmpSwipe
) AS DayNums
SET @sql = ';with p AS
(
SELECT DayNum, 1 as Present,A.emp_CDSID FROM CDSI_DB.dbo.EmpSwipe
)
SELECT emp_CDSID, ' + @select_list + ' from p
PIVOT
(
Max(Present) FOR DayNum IN ( ' + @pivot_list + ')
) AS PivotTable'

Print @sql
Execute @sql
----------------------------
Error as follows..

---when--Print..
;with p AS
(
SELECT DayNum, 1 as Present,A.emp_CDSID FROM CDSI_DB.dbo.EmpSwipe
)
SELECT emp_CDSID, [3] AS [3], [6] AS [6], [9] AS [9], [7] AS [7], [10] AS [10], [4] AS [4], [2] AS [2], [5] AS [5] from p
PIVOT
(
Max(Present) FOR DayNum IN ( [3], [6], [9], [7], [10], [4], [2], [5])
) AS PivotTable
--when--Execute
Msg 203, Level 16, State 2, Line 30
The name ';with p AS
(
SELECT DayNum, 1 as Present,A.emp_CDSID FROM CDSI_DB.dbo.EmpSwipe
)
SELECT emp_CDSID, [3] AS [3], [6] AS [6], [9] AS [9], [7] AS [7], [10] AS [10], [4] AS [4], [2] AS [2], [5] AS [5] from p
PIVOT
(
Max(Present) FOR DayNum IN ( [3], [6], [9], [7], [10], [4], [2], [5])
) AS PivotTable' is not a valid identifier.
-----

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-27 : 05:53:08

Execute (@sql)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources