antonyliu2002_at_yahoo.com wrote:
> OK, I know this has been asked many times. I've done a google search,
> but but not figure out.
>
> I have a very simple table of student information in a Oracle database
> like below, where STU_ID is student id and COLL_CD is college code and
> CLS_LEVEL is class level.
>
> STU_ID COLL_CD CLS_LEVEL
> 123 KV 1U
> 234 KV 1U
> 345 KY 1U
> 456 KN 1G
> 567 KP 1L
> 678 KV 1U
> 789 KY 1G
> 890 KY 1G
> 901 KY 1U
> 911 KP 1V
> 912 KV 1L
> 913 KN 1G
> 915 KP 1U
>
> I would like to count and list the number of students of each class
> level in each college like so:
>
> COLL_CD 1U 1G 1L 1V
> KV 23 2 0 0
> KY 9 16 88 1
> KN 13 45 55 0
> KP 0 0 61 18
>
> I know I can do
>
> Select count(*) from T_STUDENT where COLL_CD='KV' and CLS_LEVEL='1U'
>
> one after another.
>
> But can I get this crosstab in a single SQL query with SqlPlus?
>
> Thanks.
Morgan's library at www.psoug.org
click on DECODE.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Aug 25 2006 - 19:36:55 CDT
