Results 1 to 4 of 4

Thread: Help splitting values of 1 column into different columns in VFP9 using SQL

  1. #1
    Junior Member
    Join Date
    Oct 2016
    Posts
    1

    Question Help splitting values of 1 column into different columns in VFP9 using SQL

    Hello,

    is there a way to write a query to split values from a column into multiple columns and adding the total?
    I have the following table:

    Code:
    Table: cust_transactions
    pur_date      cus_name pur_type  pur_total
    01/01/2016    AMW      I         15.00
    01/03/2016    FTY      i         40.00
    01/05/2016    TEC      X         30.00
    01/06/2016    AMW      X         20.00
    01/07/2016    AMW      i         90.00
    01/10/2016    FTY      I         100.00
    01/13/2016    TEC      I         355.00
    01/18/2016    AMW      X         29.99
    01/20/2016    FTY      X         23.00
    01/20/2016    FTY      I         50.00
    01/22/2016    FTY      i         187.00
    01/30/2016    TEC      i         325.00

    I would like to get the data filtered and have the following output;

    Code:
    cus_name pur_type_i   pur_type_I  pur_type_X
    AMW      90.00	      15.00 	  49.99						
    TEC      325.00       355.00      30.00
    FTY      227.00       150.00      23.00

    the above output is splitting the values "i", "I" and "X" from 1 column into different columns and at the same time is adding its total.
    Any help will be much appreciated.

    Thank you

  2. #2
    Senior Member
    Join Date
    Feb 2012
    Location
    Rome - Italy
    Posts
    1,877
    Hi Webuxer,

    for what I know, it is not easy, that there is no "a single instruction" to do it ..

    First, you should total for "pur_type"
    select pur_date, cus_name, pur_type, sum (pur_total) from your table group by pur_date, cus_name, pur_type into cursor cur_total

    calculate how many groups you have:
    lnGroupTotal = reccount ()

    then create a cursor with the columns of the base table + lnGroupTotal

    then read the table
    select cur_total
    scan
    select curTotal
    replace pur_date with cur_total.pur_date,
    cus_name with cur_total.cus_name, pur_type with cur_total.pur_type

    then, evaluate where is the right column for the pur_type ..



    ENDSCAN

    ciao
    Fabio

  3. #3
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,121
    Hi Webuxer,

    I can not see how to get it in one pass.
    Trying with Lianja's Southwind PRODUCTS table (supplierid<3 to narrow the result, more similar to your example data)
    Equivalents:
    supplierid=cus_name,
    categoryid=pur_type,
    unitsinstock=pur_total

    Code:
    select supplierid,categoryid,unitsinstock from products where supplierid<3
    got

    Code:
    SUPPLIERID CATEGORYID         UNITSINSTOCK
    
    
              1          1             22.00000
              1          1             63.00000
              1          2             89.00000
              2          2             53.00000
              2          2              0.00000
              2          2             76.00000
              2          2            104.00000
              2          3              0.00000

    Then

    Code:
    select supplierid,categoryid,icase(categoryid=1,sum(unitsinstock),sum(0)),icase(categoryid=2,sum(unitsinstock),sum(0)),icase(categoryid=3,sum(unitsinstock),sum(0)) from products group by categoryid,supplierid having supplierid<3 into cursor FIRST_CURSOR
    got

    Code:
     SUPPLIERID CATEGORYID                  EXPR0001                  EXPR0002                  EXPR0003
    
    
              1          1                        85                         0                         0
              1          2                         0                        89                         0
              2          2                         0                       233                         0
              2          3                         0                         0                         0
    I understand that you want result:

    Code:
    SUPPLIERID    EXPR0001                  EXPR0002                  EXPR0003
    
              1          85                       89                         0
              2          0                       233                         0

    This is a step away.
    Code:
    select supplierid, sum(expr0001), sum(expr0002), sum(expr0003) from FIRST_CURSOR group by supplierid
    Put all together in Lianja's Console or in script:
    Code:
    open database southwind
    select supplierid,categoryid,icase(categoryid=1,sum(unitsinstock),sum(0)),icase(categoryid=2,sum(unitsinstock),sum(0)),icase(categoryid=3,sum(unitsinstock),sum(0)) from products group by categoryid,supplierid having supplierid<3 into cursor FIRST_CURSOR
    select supplierid, sum(expr0001), sum(expr0002), sum(expr0003) from FIRST_CURSOR group by supplierid
    Code:
     SUPPLIERID                  EXPR0001                  EXPR0002                  EXPR0003
    
    
              1                        85                        89                         0
              2                         0                       233                         0


    In MS SQL I believe it can be done by

    Code:
    select 
    case 
    when categoryid=1 then sum(unitsinstock)
    else 0.00
    end as expr0001,
    
    case 
    when categoryid=2 then sum(unitsinstock)
    else 0.00
    end as expr0002,
    
    case 
    when categoryid=3 then sum(unitsinstock)
    else 0.00
    end as expr0003,
    
    categoryid,
    supplierid  
    from products group by categoryid, supplierid
    I have no such data in MSSQL to check the script.
    Last edited by josipradnik; 2016-10-14 at 09:49.

  4. #4
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    2,068
    Hi Webuxer,

    what you are trying to do is call a crosstab.

    Here's a great article on using crosstabs with VFP: http://www.tomorrowssolutionsllc.com...Cross-tabs.PDF This is useful for getting an understanding of crosstabs, and also the use of the parameters

    We use FastxTab (as it is, indeed, faster). Here's an article that demonstrates fastxtab and extends it to use long fieldnames: http://sandstorm36.blogspot.com/2011...cross-tab.html

    The download for fastxtab is: https://www.universalthread.com/Show...m.aspx?ID=9944

    enjoy,

    Hank
    Last edited by HankFay; 2016-10-14 at 09:53.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us