PIVOT Query Incorrect syntax near '('.

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.

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]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources