Hi all! Further to thread thread

[Nifrabar]

Hi all!

Further to thread thread184-1663610:
One of my users should like to get in a report on one row fields of seperate records.
Although I presume there is not such an SQL_function I like to give it a try:

So table is:

ID - Client - RemarkID - [left outer Remark]

1 3 12 Friend
2 7 11 Family
3 3 1 Neighbour
4 3 2 Collegue
5 1 11 Family
6 4 5 Nephew


So an SQL function should result in:

3 Friend,Neighbour, Collegue
7 Family
4 Nephew

Does such exists or should I create a cursor using Scan Endscan?

-Bart

 

[Nifrabar]

Oops, forgot the name of this thread.....

 

[TamarGranor]

If this is VFP data, then you need to use SCAN to consolidate. In SQL Server, there is a way to do this.

Tamar

 

[Dan Freeman]

This is a crosstab, isn't it?

Select Client, RemarkID, Lookup(*see below) as Remark into cursor Temp Order by 1,2,3
Do (_Genxtab)

*Where you use Lookup() or even a UDF() or a subquery to lookup the related Remark from RemarkID

 

[Nifrabar]

Tamar,
Yes this is VFP

Danfeeman,
What's _Genxtab?

TIA
-Bart

 

[Mike Lewis]

Bart,

_Genxtab is a system variable that contains a pointer to VFPXTAB.PRG, which is the cross-tab generator. For more information, see the VFP Help topic on the Cross-Tab Wizard.

Where you use Lookup() or even a UDF() or a subquery to lookup the related Remark from RemarkID

Keep in mind that LOOKUP() is not optimisable, so this approach could have a negative effect on performance - which might not be an issue, of course.

Also, be aware of the limitations of using UDFs in a SQL query (see : Are UDFs safe?).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

Keep in mind that LOOKUP() is not optimisable

This is one of those urban legends that is absolutely true, comes directly from the help file, but has been completely misinterpreted to the point of being bad-but-widely-accepted advice. Read the NEXT sentence in the help file: if you use the 4th parameter (the index tag name), the index is used for a SEEK which doesn't *NEED* optimization.

The point of my post, though, is to use whatever method you need (which could actually be using the good old WHERE clause for a two-table join) to get the value from a related table into that third column and then call the crosstab generator.

 

[Mike Lewis]

This is one of those urban legends that is absolutely true, comes directly from the help file, but has been completely misinterpreted to the point of being bad-but-widely-accepted advice.

Well, I always was a sucker for urban legends. Never mind. It's been more than a decade since I used the function.

But I accept your more general point, about using whatever method is appropriate.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Nifrabar]

Currently i don't have access to VFP as i have a weekend off. So will come back early next week.
-Bart

 

[Nifrabar]

I've had a look at the vfpxtab.prg but I presume that for my particular solution some custom code might workout some quicker....
Thanks for pointing me to this prg which I recognise from the excel pivot-function.

-Bart