We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


Pivot question

DowntownIT asked
Medium Priority
Last Modified: 2012-05-11
Trying to use the pivot function which may not be the right function to use anyways but here is the issue.

I have the following result set.

Amb_no        season_desc        event_no

1                    A                           100
1                    A                           101
2                    A                           100
3                    A                           100
3                    A                           101
3                    A                           102
1                    B                           103
1                    B                           104
2                    B                           103
3                    B                           103
3                    B                           104
3                    B                           105

What I am looking for in the end result is:

Amb_no        A        B      
1                   2        2 <count(event_no)>
2                   1        1
3                   3        3

The trick is that the number distinct season_desc may change. one time I may have only 2 and the next time I may have 5. In this case my column header would look like

Amb_no        A        B        C         D        E

Thanks for the help.  
Watch Question

select Amb_no, [A], [B], [C], [D], [E]
from 	(
	select Amb_no, season_desc, event_no from yourtable
	) o
pivot (count(event_no) for season_desc in ([A], [B], [C], [D], [E])) as p

Open in new window


My only issue with this approach is that I don't not know what the values of season_desc are going to be. One user may select only A and B and the next user may select C, D, and E. So I am not able to hard code the season_desc column values into the procedure. It is this reason that I am thinking that Pivot won't work for me.

Unlock this solution and get a sample of our free trial.
(No credit card required)


Worked Brilliantly!!

Thank you so much for the help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.