Re: how to combine columns?

Thanks Michel!!

On Tue, 17 Jul 2001 17:58:50 +0200, "Michel Cadot" <micadot_at_netcourrier.com> wrote:
>v815> create table games (gdate date, ourscore integer, theirscore integer);
>v815> insert into games values (to_date('2001-02-01','YYYY-MM-DD'),23,49);
>v815> insert into games values (to_date('2001-02-03','YYYY-MM-DD'),19,14);
>v815> insert into games values (to_date('2001-02-08','YYYY-MM-DD'),34, 7);
>v815> insert into games values (to_date('2001-02-20','YYYY-MM-DD'), 9, 3);
>v815> insert into games values (to_date('2001-02-28','YYYY-MM-DD'),12,12);
>v815> commit;
>v815> select to_char(gdate,'YYYY-MM-DD') "Date", ourscore, theirscore
> 2 from games
> 3 /
>
>Date OURSCORE THEIRSCORE
>---------- ---------- ----------
>2001-02-01 23 49
>2001-02-03 19 14
>2001-02-08 34 7
>2001-02-20 9 3
>2001-02-28 12 12
>
>5 rows selected.
>
>v815> select to_char(gdate,'YYYY-MM-DD') "Date",
> 2 decode (sign(ourscore-theirscore),-1,'L',1,'W','T') S
> 3 from games
> 4 /
>
>Date S
>---------- -
>2001-02-01 L
>2001-02-03 W
>2001-02-08 W
>2001-02-20 W
>2001-02-28 T
>
>5 rows selected.
>
>v815> select sum(decode(sign(ourscore-theirscore),1,1,0))||'-'||
> 2 sum(decode(sign(ourscore-theirscore),-1,1,0))||'-'||
> 3 sum(decode(sign(ourscore-theirscore),0,1,0)) S
> 4 from games
> 5 /
>
>S
>----------------
>3-1-1
>
>1 row selected.
>
>--
>Have a nice day
>Michel
 

-- 
Scott Crevier
http://www.crevier.org/