SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 PIVOT Query Incorrect syntax near '('.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ezayas
Starting Member

1 Posts

Posted - 12/19/2012 :  18:17:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/19/2012 :  19:34:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/19/2012 :  21:57:27  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 12/24/2012 :  07:29:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000