Pivot question

Posted on 2011-04-25
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.  
Question by:DowntownIT
    LVL 41

    Expert Comment

    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

    LVL 2

    Author Comment

    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.

    LVL 41

    Accepted Solution

    in that case you can do it dynamically, check the below:

    declare @strSQL varchar(max)
    declare @cols varchar(max)
    set @cols = stuff((select distinct  '], [' + season_desc 
    			from yourtable order by 1 for xml path('')), 1, 2, '') + ']'
    set @strSQL = 'select Amb_no, ' + @cols + ' from (
    		select Amb_no, season_desc, event_no from yourtable) o
    		pivot (count(event_no) for season_desc in (' + @cols + ')) as p'

    Open in new window

    LVL 2

    Author Closing Comment

    Worked Brilliantly!!

    Thank you so much for the help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now