Urgent...Dynamic Pivot error Please help!!!

Hi there,

I am trying to get this work but in vain, really worried! Any help is very much appreciated!

I have 2 tables, table1 and table2.

Table 1
usrId usrFirstName usrLastName other fields...
1 Adam Cadman ...
1 Adam Cadman ...
2 Dana Jones ...
2 Dana Jones ...

Table2
resultId Result other fields...
1 Appt set ...
1 Appt set ...
1 (call back) ...
2 (call back) ...
2 confirm appt ...
3 (call - person - back) ...
3 appt at 6 (1/1/12) ...





Now I want to join these two tables, and want to convert rows into columns using dynamic pivot operation. It should basically give the count of each result for distinct user.

Desired result set

usrFirstName usrLastName Appt set (call back) ...
adam cadman 2 1
dana jones 0 1
.... .... ... ...


When I execute the code below, it gives me the message that certain number of rows are affected, but also gives some error.
Please find everything below:




SELECT DISTINCT Result as 'Result'
INTO #results
FROM table2

DECLARE @resultList nvarchar(max)

SELECT @resultList = COALESCE( @resultList + ', ', '') + CAST( QUOTENAME( Result ) AS VARCHAR(1000) )FROM
#results
ORDER BY Result

--select * from #results

DROP TABLE #results

-- this var will hold the dynamic PIVOT sql
DECLARE @pvt_sql nvarchar(max)
SET @pvt_sql = 'SELECT *
FROM
(SELECT Result, usrFirstName, usrLastName
FROM table1 Inner join table2 on table1.usrId = resultId
) AS data
PIVOT
(
COUNT( Result )
FOR Result IN
( ' + @resultList + ' )
) AS pvt'

-- run the query

EXEC sp_executesql @pvt_sql

------------------------------------


So when I execute this, I get the following:

(384 row(s) affected)
Msg 1038, Level 15, State 4, Line 10
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.


i think the problem is that the Result list has entries like (call - back) etc with ( and ) inside. So, is it the delimiters problem in here that is causing this error?
Or is it something related to aliases?

Please help me, i have been struggling with this since many days. Thank you very much in advance.