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 2005 Forums
 Transact-SQL (2005)
 PIVOT Query Incorrect syntax near '('.

Author  Topic 

ezayas
Starting Member

1 Post

Posted - 2012-12-19 : 18:17:03
I have been getting this error when executing the PIVOT query any help would be appreciated.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near '('.

Data ##temp1 table columns are (Member_ID,question_text,response)
Member_ID question_text response
0562773*01 In general would you say your health is? 2
0562773*01 How would you rate your health today? 1
0562773*01 How would you rate your emotional health now? 1
9962773*01 In general would you say your health is? 5
9962773*01 How would you rate your health today? 1
9962773*01 How would you rate your emotional health now? 3

--PIVOT Query
DECLARE @FieldList VARCHAR(MAX)
SELECT @FieldList = STUFF(( SELECT

'],[' + question_text

FROM ##temp1

FOR XML PATH('')

), 1, 2, '') + ']'
SELECT Member_ID

FROM ( SELECT t1.question_text
, t1.response
FROM ##temp1 AS t1

) p

PIVOT ( ([response]) FOR question_text IN ( @FieldList ) ) AS pvt
ORDER BY Member_ID;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-19 : 19:34:19
Two things:

1. You have to use an aggregate function for value to be pivoted.
2. You cannot use a variable for the pivot list.
....
PIVOT ( MAX([response]) FOR question_text IN ( @FieldList ) ) AS pvt
....

You may need to use dynamic pivoting to do what you are trying to do. See Madhivanan's blog.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-19 : 21:57:27
[code]Create table #temp
(Member_ID Varchar(30),question_text Varchar(100),response int)

Insert into #temp

Select '0562773*01','In general would you say your health is?',2 union all
Select '0562773*01','How would you rate your health today?',1 union all
Select '0562773*01','How would you rate your emotional health now?',1 union all
Select '9962773*01','In general would you say your health is?',5 union all
Select '9962773*01','How would you rate your health today?',1 union all
Select '9962773*01','How would you rate your emotional health now?',3

--PIVOT Query
Declare @SQL NVarchar(Max)
DECLARE @FieldList VARCHAR(MAX)
SELECT @FieldList =
STUFF(( SELECT distinct '],[' + question_text FROM #temp
FOR XML PATH('')
), 1, 2, '') + ']'


Set @SQL =
'SELECT Member_ID,' + @FieldList + '
FROM
(
SELECT Member_ID,question_text,response
FROM #temp
)P
PIVOT (MAX(response) for question_text in ( ' + @FieldList + '))as pvt'

EXEC (@SQL)

Member_ID How would you rate your emotional health now? How would you rate your health today? In general would you say your health is?
0562773*01 1 1 2
9962773*01 3 1 5
[/code]
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-12-24 : 07:29:04
You may be interested to read this too http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -