asked
How to get average of two columns in PIVOT?
In my table i want to get the average value of two columns PackerID HeaderID Price Quantity Size 1 1 10 10 50 2 1 10 10 60 3 1 8.5 20 50 Price is per 1 quantity* from above table i want to get average price of Size '50' using PIVOTE.
Any solution should work for 1 row, 10 rows or 1000+ rows - what issue specifically do you have?
If you look at the [PIVOT documentation][1], you'll see that one of the examples is an AVERAGE pivot. If you can build an intermediate table with the unpivoted data, then feed that into the PIVOT, you should be fine. If you want more help, then you'll need to explain exactly what you want and how you want it calculated... [1]:
http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Why use `pivot`? I can get the average per size using a aggregate windowing function declare @t table (packerid int, price decimal(8,2), quantity int, size int) insert into @t select 1,10,10,50 insert into @t select 2,10,10,60 insert into @t select 3,8.5,10,50 select distinct size, avg(price)over(partition by size) from @t gives 50 9.250000 60 10.000000
Yeah, that was going to be another question, but I'm feeling a bit manflu-y, so didn't remember to write that in... ...but, in my defence, I did ask for full details of what he wanted to do.
10 People are following this question.
