Link to home
Start Free TrialLog in
Avatar of DowntownIT
DowntownITFlag for United States of America

asked on

Pivot question

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.  
Avatar of ralmada
ralmada
Flag of Canada image

try
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

Avatar of DowntownIT

ASKER

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.

Thanks,
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Worked Brilliantly!!

Thank you so much for the help.