none
tabel field convert in colum RRS feed

  • Question

  • below is my tabel

    supplier name    description         amount

    suppA                   Purchase           100.00

    suppB                   utilities                50.00

    SuppC                   tax                     20.00

    suppD                  other exp            50.00

     

    i would like result  as below

    supp name         purchase     other exp    tax      utilities

    suppA                  100.00

    suppB                                                                    50.00

    suppC                                                       20.00

    suppD                                     50.00

     

    description is diffrent every month

    please help

    Sunday, February 5, 2012 4:17 PM

Answers

  • Your prior command ( FROM ('c:\shiv\data\'),; ends with the , which is wrong. Also, you're not providing a valid dbf file name, so the above will not work. You need to use the actual dbf file, not just the directory with the data.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Monday, February 6, 2012 1:07 PM
    Moderator

All replies

  •  

    select Supplier,
    
    SUM(IIF(Description = 'Purchase', amount, cast(0 as Currency)) as Purchases,
    
    SUM(IIF(Description = 'Other exp', amount, cast(0 as Currency)) as "Other Expenses",
    
    SUM(IIF(Description = 'Tax', amount, cast(0 as Currency)) as Tax,
    
    SUM(IIF(Description = 'Utilities', amount, cast(0 as Currency)) as Utilities
    
    FROM myTable
    
    GROUP BY Supplier
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, February 5, 2012 4:22 PM
    Moderator
  • thanks for reply and u help me a lot in past

      the problem is description every month change  and add more by user

    thanks


    • Edited by NILKAMAL Sunday, February 5, 2012 4:27 PM
    Sunday, February 5, 2012 4:27 PM
  • In this case, how exactly do you want to group values? How may distinct descriptions do you have? The problem you're looking for is called pivot and there is a class in UniversalThread Downloads section called FastXTab you can use.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, February 5, 2012 4:29 PM
    Moderator
  • thanks let me check with fastxtab

    thanks'

     

    Sunday, February 5, 2012 4:37 PM
  • Hi!

     

    SUM(IIF(Description = 'Purchase', amount, cast(0 as Currency)) )as Purchases,
    in this line i get error command is missing required clause

    • Edited by NILKAMAL Sunday, February 5, 2012 5:51 PM
    Sunday, February 5, 2012 5:37 PM
  • Post the whole statement you tried. To me the above looks perfectly fine.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, February 5, 2012 7:51 PM
    Moderator
  • SELECTcashpaid.date,cashapid.name,;
    SUM(IIF(Descri ='PURCHASE', capaid_amt, cast(0 as Currency)))as Purchases,;
    FROM ('c:\shiv\data\'),;
    GROUP BY date,name

    error:  command contains unrecognized phrase/keyword (hilighted group by)

    Monday, February 6, 2012 11:59 AM
  • Your prior command ( FROM ('c:\shiv\data\'),; ends with the , which is wrong. Also, you're not providing a valid dbf file name, so the above will not work. You need to use the actual dbf file, not just the directory with the data.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Monday, February 6, 2012 1:07 PM
    Moderator
  • thanks

    it works fine

    • Marked as answer by NILKAMAL Monday, February 6, 2012 1:31 PM
    • Unmarked as answer by Martin_Xie Wednesday, February 8, 2012 1:42 AM
    Monday, February 6, 2012 1:31 PM