"manaparai vincent" <vincentst***@gmail.com> wrote in message
news:1157642111.927111.45490@e3g2000cwe.googlegroups.com...
> hi,
>
>       i met one problem in sql server my
>
>                                        table name is        : student
>                                        Field1                   : name
>                                        Field2                   : rank
>                                        Field3                   : exam
> for example
>
>                    name        rank        exam
>
>                     anbu         1            q
>                     raja           2            q
>                     kumar       3            q
>                     ramesh     4            q
>                     raja           1            h
>                     anbu         2             h
>                     kumar       3            h
>                     ramesh     4            h
>                     anbu          1            a
>                     raja            2            a
>                     ramesh       3            a
>                     kumar        4            a
>
> q=> quart exam,     h=>half exam      a=> anual exam
>
> my question anbu how many times get 1st rank and 2nd upto 4th rank
> output format is
> --------------------------------------------------------------------------
-----------------------------------------
>                                   name          count(1)  count(2)
> count(3)  count(4)
> --------------------------------------------------------------------------
----------------------------------------
>                                    anbu              2             1
>          0         0
>                                    raja                1             2
>            0         0
>                                    kumar            0             0
>         2          1
>                                    ramesh           0            0
>        1          2
> --------------------------------------------------------------------------
---------------------------------------------
>
> similar to excel pivot table. how to this solve in sql server. I want
> this query very urgent.
>
> Thank u
>
> by
>
> vincent
> manapparai
>

> First, return the results without the counts.  We can use the case statement
> to get each of the ranks into a different column, with a zero or one
> indicating if that is the rank for the current row.
>
> select
> name
> , rank
> , exam
> , case when rank = 1 then 1 else 0 end as "First"
> , case when rank = 2 then 1 else 0 end as "Second"
> , case when rank = 3 then 1 else 0 end as "Third"
> , case when rank = 4 then 1 else 0 end as "Fourth"
> from student
>
> Now, if we SUM each of the 4 columns by the student name, then we will get
> the number of times they have attained each rank.  We can make the above
> into a view and query against it, or do it all in one SQL as follows:
>
> select
> name
> , sum(case when rank = 1 then 1 else 0 end) as "First"
> , sum(case when rank = 2 then 1 else 0 end) as "Second"
> , sum(case when rank = 3 then 1 else 0 end) as "Third"
> , sum(case when rank = 4 then 1 else 0 end) as "Fourth"
> from student
> group by name
>
> "manaparai vincent" <vincentst***@gmail.com> wrote in message
> news:1157642111.927111.45490@e3g2000cwe.googlegroups.com...
> > hi,
> >
> >       i met one problem in sql server my
> >
> >                                        table name is        : student
> >                                        Field1                   : name
> >                                        Field2                   : rank
> >                                        Field3                   : exam
> > for example
> >
> >                    name        rank        exam
> >
> >                     anbu         1            q
> >                     raja           2            q
> >                     kumar       3            q
> >                     ramesh     4            q
> >                     raja           1            h
> >                     anbu         2             h
> >                     kumar       3            h
> >                     ramesh     4            h
> >                     anbu          1            a
> >                     raja            2            a
> >                     ramesh       3            a
> >                     kumar        4            a
> >
> > q=> quart exam,     h=>half exam      a=> anual exam
> >
> > my question anbu how many times get 1st rank and 2nd upto 4th rank
> > output format is
> > --------------------------------------------------------------------------
> -----------------------------------------
> >                                   name          count(1)  count(2)
> > count(3)  count(4)
> > --------------------------------------------------------------------------
> ----------------------------------------
> >                                    anbu              2             1
> >          0         0
> >                                    raja                1             2
> >            0         0
> >                                    kumar            0             0
> >         2          1
> >                                    ramesh           0            0
> >        1          2
> > --------------------------------------------------------------------------
> ---------------------------------------------
> >
> > similar to excel pivot table. how to this solve in sql server. I want
> > this query very urgent.
> >
> > Thank u
> >
> > by
> >
> > vincent
> > manapparai
> >