🚀 go-pugleaf

My-Phuong L Tran (mtran2@osf1.gmu.edu) wrote:
: In article <3m9m8h$k1a@news2.delphi.com> XIAOMUNSON@news.delphi.com (XIAOMUNSON@DELPHI.COM) writes:
: >jtmurray@netcom.com (john murray) writes:
: >
: >>	Using Transact SQL, I need to return a table like this:
: >>====================================
: >>FIELDA       FIELDB       FIELDC
: >>====================================
: >>B             100          150
: >>C             210          350
: >>D             145           0
: >>====================================
: >
: >>	from a table similar to the one listed below.  This selects the 
: >>value of Col1 and provides the sum of Col3 into either FieldB or FieldC 
: >>based on the value of Col2 (i.e. if Col2 is 1 then the Sum of Col3 into 
: >>fieldB...etc..)
: >
: >>Table...
: >>===============================
: >>Col1      Col2        Col3
: >>===============================
: >>B           1          100
: >>B           2          150
: >>C           1          210                           
: >>C           2          350
: >>D           1          145
: >>===============================



: One way to do this using self joins (and Oracle syntax) is

:    select t1.col1, t1.col3, t2.col3
:      from temp1 t1, temp1 t2 where t1.col1 = t2.col1
:      AND t1.col2 = '1' AND t2.col2 = '2' 

No... no... no.

What is needed is a "pivot" of the table: make a "horizontal" table out of
a "vertical" one.  (FYI- This method was presented in a white paper entitled
"Linguistic Optimization" at last year's International Sybase User's 
Conference.)

Try this:

select FIELDA = col1
     , FIELDB = sum(col3 * (1 - abs(sign(col2 - 1))))
     , FIELDC = sum(col3 * (1 - abs(sign(col2 - 2))))
  from table1
 group by
       col1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    _/_/_/  _/_/_/  _/      _/  |  Bill Mallary, DBA/Consultant     Houston, TX
   _/   _/   _/    _/      _/   |  Sybase/UNIX/C/Powerbuilder
  _/_/_/    _/    _/      _/    |  Email: bmalla@phoenix.net
 _/   _/   _/    _/      _/     |  Home: (713) 326-3230
_/_/_/  _/_/_/  _/_/_/  _/_/_/  |  finger bmalla@phoenix.net | pgp -kfa +force
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~