Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.media.kyoto-u.ac.jp!newsfeed.icl.net!newsfeed.fjserv.net!news.tele.dk!news.tele.dk!small.news.tele.dk!sn-xit-02!sn-xit-10!sn-xit-06!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
From: DA Morgan <damorgan@x.washington.edu>
Newsgroups: comp.databases.oracle.misc
Subject: Re: tricky SQL
Date: Thu, 03 Mar 2005 08:23:33 -0800
Organization: Ye 'Ol Disorganized NNTPCache groupie
Message-ID: <1109866823.576829@yasure>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax)
X-Accept-Language: en-us, en
MIME-Version: 1.0
References: <1109863006.386175.88210@z14g2000cwz.googlegroups.com>
In-Reply-To: <1109863006.386175.88210@z14g2000cwz.googlegroups.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Cache-Post-Path: yasure!unknown@oracle.advtechserv.com
X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/)
X-Complaints-To: abuse@supernews.com
Lines: 34
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:116964

bcjm wrote:

> I have a table with 3 columns: Person, Class and grade. There are
> unlimited number of classes available but each person can only take 3
> classes max.  How do you right one SQL to show the list of class and
> grade of each person in one row?
> 
> Example: Table T
> 
> p1 c1 A
> p1 c2 D
> p1 c3 B
> p2 c5 B
> p3 C1 A
> p3 C8 C
> ...
> ...
> 
> SQL result
> P1 C1 A C2 D C3 B
> P2 C5 B
> P3 C1 A C8 C

http://www.psoug.org
click on DECODE
search for cross-tabbing with DECODE which can also be done with CASE.

And yes you should have paid more attention in class when it was
discussed. ;-)
-- 
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)