×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

sipliting data

sipliting data

sipliting data

(OP)
hello
I have table with 2 fields : amount and id
like this
amount id
1000 1
1700 1
1800 2
I need to sort amount by id like this
feild1 feild2 id
1000 1
1700 1
1800 2
how can i do this
thank

RE: sipliting data

It depends on what you want to do with the table after it is sorted.

If you merely want to temporarily re-sort it for use within a PRG (SCAN/ENDSCAN, DO WHILE/ENDDO, etc.) you can just build an Index on the table using an Expression which gives you what you want.
If you are unsure how to build an Index you might want to look at:
Free VFP Tutorial Videos
Of particular interest might be the video named: Indexing Records

If you need a COPY of the table organized in another manner you can build a copy using SQL commands such as:

CODE -->

SELECT * ;
FROM MyTable;
WHERE <whatever>;
ORDER BY <whatever>;
INTO TABLE/CURSOR Results 

Good Luck,
JRB-Bldr

RE: sipliting data

(OP)
thank JRB-Bldr

I need like this report

RE: sipliting data

So you are saying that you want separate columns for Field 1 and Field 2 respectively. That's OK. You can do that. But will there always be just those two fields? Or might you also have Field 3, Field 4, ...., and so on? If so, what happens if you have more fields than will fit across the width of the page?

If you could define your problem more precisely, we will be better able to help.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: sipliting data

I agree a more precise description would help. At least now your grpahic shows, what your initialpost didn't. You seem to want to split data depending on ID. There's no such thing as SELECT amount AS ("field"+transform(id)), even if VFP would allow that, it does use the first result row only to build up the result cursor structure in regard of field data types, so that would likely apply to field names and only one field would be created.

Something, I would again not solve by SQL, but via a) preparing a result cursor with all necessary fields, b) scan through data and sort in amount to fieldX in dependence on ID.

A simple idea may be to create insert statements and let the number of 0, blank or NULL values including commas before the amount depend on ID, so the amounts sort into the right column.

Bye, Olaf.

RE: sipliting data

(OP)
the data siplit just to 2 feilde by 1 or 2 exactly like this report

RE: sipliting data

Quote (pcwaleed)

data siplit just to 2 feilde by 1 or 2 exactly like this report

But you don't say specifically what is the differentiating criteria for what should go into Field1 and what should go into Field2

From your graphic image above ID=1 causes the associated value to go into Field1
And ID=2 causes the associated value to go into Field2

Is that correct for ALL records encountered?

CODE -->

SELECT IIF(ID=1, TRANSFORM(Amount,"999999"), REPLICATE(" ",6)) AS Field1,;
IIF(ID=2, TRANSFORM(Amount,"999999"), REPLICATE(" ",6)) AS Field2,;
ID;
FROM MyTable;
INTO CURSOR Results READWRITE

Let us know if that is how things needs to operate so that we can advise you better.

Good Luck,
JRB-Bldr


RE: sipliting data

(OP)
thank i will tray that

RE: sipliting data

You can just forget that, if you don't know what and/or how many IDs your data has in advance.

This doesn't even look like normal pivoting or crosstab wizard demands.
Notice VFP capacities limit you to 255 field tables and cursors since one of them will be ID, you're limited to field1-field254 to spread out your amounts.
If your final goal is a report or spreadsheet, use the data as is to automate excel to put in the amount at cell(row,id), that'll be much easier than first creating an SQL result, also the column limit is quite unlimited in Excel.

Bye, Olaf.

RE: sipliting data

If your ID can only ever be 1 or 2 (so, you never have more than three columns in your report) AND if your only goal is to create the report shown in your second post, then JRB has given you a good solution. I would just make two small adjustments to his code.

First, the cursor needs to be in ID order. Second, you could slightly simplify the code, as follows:

CODE -->

SELECT IIF(ID=1, Amount, 0) AS Field1, ;
       IIF(ID=2, Amount, 0) AS Field2, ;
       ID;
FROM MyTable ;
ORDER BY ID ;
INTO CURSOR Results READWRITE 

In the report, you might want to set the field properties for Field1 and Field2 to Numeric / "Blank if zero" (on the Format tab).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: sipliting data

(OP)
Iam Sorry for the delay, the second iif statement return star like this

can you help me please

RE: sipliting data

That's because of what I said earlier, VFP uses the first result row to determine field types and widths. While 0 is an int and not limited to just one char,the * indicates field2 has limited size because ot was 0 in the first row.

Try CAST:

CODE -->

SELECT CAST(IIF(ID=1, Amount, 0) as int) AS Field1, ;
       CAST(IIF(ID=2, Amount, 0) as int) AS Field2, ;
       ID;
FROM MyTable ;
ORDER BY ID ;
INTO CURSOR Results READWRITE 

If int doesn't fit amount type, adjust it as needed (eg use double or currency or whatever)

Bye, Olaf.

RE: sipliting data

(OP)
mr.Olaf Doschke Iam Very grateful to you and to all for the help..

RE: sipliting data

Quote:

the second iif statement return star like this

Sorry, that was my mistake. Olaf has given you the correction.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: sipliting data

(OP)
thank mika and any one help me

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close