🚀 go-pugleaf

In article <jtmurrayD6GtKv.E8C@netcom.com>, jtmurray@netcom.com (john
murray) wrote:

>         Pardon me for sounding like a newbie here, but I am really stuck 
> with trying to write a SQL statement (listed below).  I am trying to do 
> this in C++ currently to go against a MSSQL Server so I am stuck (as far 
> as I know with Transact).  If someone has other suggestions as to another 
> product that would give me additional ability with my SQL statements, I 
> would be more than all ears..as it were.  Many TIAs for anyone that could 
> help or provide guidance on this.
> 
> ================
> 
>         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
> ===============================
> 
> John Murray 
> jmurray@dnasunnet.dna.mci.com
> 
> 
Assuming you do not want to do a multiple passes, the trick is to
get a value of zero or one to multiply col3 in order to get FIELDB,C or D.

Two versions, first Col2 is integer:

 col1  col2         col3 
 ----  ----  ----------- 
 B        1          100 
 B        2          150 
 B        3          330 
 C        1          210 
 C        2          350 
 D        1          145 
 D        3          600 
 B        2          800 

The query COULD look like this:

SELECT 
fielda=col1, 
fieldb=sum(col3 * floor(col2/1) * floor(1/col2)),
fieldc=sum(col3 * floor(col2/2) * floor(2/col2)),
fieldd=sum(col3 * floor(col2/3) * floor(3/col2))
from #t1
group by col1

Results: 
 fielda       fieldb       fieldc       fieldd 
 ------  -----------  -----------  ----------- 
 B               100          950          330 
 C               210          350            0 
 D               145            0          600 


If col2 is character, try this:

SELECT 
fielda=col1,
fieldb=sum(col3 * charindex("1",col2)),
fieldc=sum(col3 * charindex("2",col2)),
fieldd=sum(col3 * charindex("3",col2))
from #t2
group by col1


Hope this helps.