pivot t-sql adding a percentage column

in a cross table result is very common to add a percentage column in respect

of the sum of another column

for example

x z%

a 2 0,46 = 2/(2+5+6)

b 5 0,38 = 5/(2+5+6)

c 6 0,15 = 6/(2+5+6)

is there an easy way to do that in t-sql using the pivot command ?

This was going to be my first answer

"Not easily that I can think of because it requires knowledge of the other rows in the result set and that is where SQL is not very good. That is performing calculations at two levels one aggregate is across th

You need to find the total and then calculate the percentage. This is normally done using a derived table"

However in digging I looked into windowing. This is a new feature that can be used with the new ranking functions as well as the normal aggregates.

In your situation you can do

select x, 1.0*x / sum(x) over (parition by 1) percentage

from mytable

What this does is that it performs a sum of x over the partition of 1 (as this is the same for each row it does a sum across the whole resultset).