sipliting data

[pcwaleed]

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

 

[JRB-Bldr]

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:

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

Good Luck,
JRB-Bldr

 

[pcwaleed]

thank JRB-Bldr

I need like this report

 

[Mike Lewis]

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

 

[Olaf Doschke]

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.

 

[pcwaleed]

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

 

[JRB-Bldr]

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?

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

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

Good Luck,
JRB-Bldr

 

[pcwaleed]

thank i will tray that

 

[Olaf Doschke]

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.

 

[Mike Lewis]

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:

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

 

[pcwaleed]

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

can you help me please

 

[Olaf Doschke]

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:

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.

 

[pcwaleed]

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

 

[Mike Lewis]

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

 

[pcwaleed]

thank mika and any one help me