I replied too soon. I need the result in one record and that solution gives the result in multiple records. Any other solution?

Hi all,

I found the solution and it is as simple as(without any commas between pivots and the field names has to be unique):

Select * from (select * from tbl) p

PIVOT(MAX(col1)...

I could not convert it to string because some of the data is not in decimal format. So my issue is now resolved.

Thanks for your help.

Sartori,

I liked your alternate solution better. It converts to string, but how do I convert the result back to decimal?

Thanks.

I should have searched on this site before posting, I apologize. I found the solution on this site as:

REPLICATE('0',(3-len(floor(Entry))))+ cast(Entry as nchar(20))

Thanks to whoever contributed the solution.

DISTINCT cannot work in this case but wrapping the query in select clause does work. Thanks to Sean and David.

Thanks again.

Any help will be appreciated.

Thanks.

Thanks JonFox, that is what I was looking for. Thanks again.

Thanks.

I found an example on BOL as Lynn stated and is working now. The first parameter to sp_executesql has to be Nvarchar and not varchar.

Thanks.

Painfully, I got it working. The query is much more complex than the columns are gave you. What I end up doing is, I created another query that would give...

What I need is another column showing percentage of each of record by

like

Convert(float,AVG(MyScale)) * 100 / SUM(AVG(MyScale))

but since I am using AVG, I cannot use SUM at the same...

I enterd the data but it got rid of the space. the Id is just an int column, myScales is a float, scale is a 1 digit column and rest...

I got working with the first solution from GSquared. Thanks.

Now i have a result as follows:

ID myScales Scale ...

Hi GSquared,

That worked perfectly! Thank you for your time.