quote:

---

Originally posted by fbaker

(SQL Server 2005)
I have the following query (trying to execute in Managment Studio):

SELECT * FROM
(SELECT Rate, Lender, Pricing, Max(Pricing) AS Maximum, Min(Pricing) As Minimum, Avg(Pricing) As Median
FROM [10_Tier].[dbo].[Temp10Tier_1000]) m
PIVOT (Sum(Pricing) For Lender IN (columnlist))p

I get the error: Incorrect syntax near ')' Microsoft SQL Server Error 102

I've been looking all over and have found many examples but I cannot get any of them to work.

What I want to accomplish is to be able to create a pivot query and use it in the "Query Builder" when designing a report for the reporting services, so that I can have a table that mimicks a "Matrix" - so that I can have multiple value columns.

---

quote:

---

Originally posted by fbaker

Ok, I was able to get the query to work by specifying a lender I knew was in the table in the IN clause. Thank you.

I have another question but I should give you some background first. I'm designing a report for a web site, there is a web page that generates the data in this Temp10Tier_1000 table based on their selections. I will need the report to use the query I got working - which is fine for design time - but when I go to plug it into my web page and dynamically asign the report data, how do I do what you are saying by storing the query in a string and executing it dynamically?

I don't know if I'm making sense. The only way I can see doing it is by querying the table first in my code to see what lenders there are, and then build the IN part of the SQL Statment by looping through the lenders available.

---

DECLARE @Lenderlist varchar(2000),@Sql varchar(8000)

SELECT @Lenderlist= LEFT(ll.Lenderlist,LEN(ll.Lenderlist)-1)
FROM
(SELECT Lender + ',' as [text()]
 FROM [10_Tier].[dbo].[Temp10Tier_1000]
 FOR XML PATH(''))ll(Lenderlist) 

SELECT @Sql='SELECT * FROM
(SELECT Rate, Lender, Pricing, Max(Pricing) AS Maximum, Min(Pricing) As Minimum, Avg(Pricing) As Median
FROM [10_Tier].[dbo].[Temp10Tier_1000]) m
PIVOT (Sum(Pricing) For Lender IN ([' + REPLACE(@Lenderlist,',','],['+']))p'

EXEC(@Sql)