June 9, 2008 at 4:57 am
I have a table of the following format
ID Col DArea Val
-----------------------------------
1 K 100 0
2 C 100 0
3 M 100 0
4 Y 100 0
5 K 98 2
6 C 98 3
7 M 98 4
8 Y 98 1
9 K 100 1
10 C 100 0
11 M 100 0
12 Y 100 0
13 K 98 2
14 C 98 5
15 M 98 4
16 Y 98 1
Now I need to calculate the average of the Val column grouped by DArea for the colors. I want the data to be in this format.
DArea K C M Y
------------------------------------------------------
100 0 0 0 0
98 1 1.5 2.5 1
How do I do this? Any help will be appreciated.
June 9, 2008 at 5:13 am
HI,
If only you were using SQL2005 you could use the new PIVOT functionality.
However you are going to have to use Case statements to Pivot your query.
Do a search for cross tab queries and see what you come up with.
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 9, 2008 at 10:35 pm
Thanks. Got the desired result. I am calculating the average values for the column. I want to round this off to 3 digits. If I give the round function in the PIVOT query [PIVOT (round(avg(dotgain)) ] it gives an error saying "round is not recognized as an aggregate function". How do I do this?
June 9, 2008 at 11:27 pm
Hi
In the pivot function you can use only aggregate functions like avg,count etc. So you will have to do the rounding part in the outer query.
BTW are you working on SQL 2000 or 2005. this is sql 2000 forum.
"Keep Trying"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply