Board index » delphi » How to simulate Crosstab query (TRANSFORM function from SQL server)

Wabbo

Delphi Developer
Mon, 24 May 2004 22:50:03 GMT

How to simulate Crosstab query (TRANSFORM function from SQL server)

Hi!
Have to port one application from SQL server to Interbase. In SQL server
they are using "TRANSFORM" function in SQL statement to get Crosstab query.
Any idea how to implement that in Interbase?
Regards,
FDF
 
savi

Delphi Developer
Tue, 25 May 2004 01:51:20 GMT

Re:How to simulate Crosstab query (TRANSFORM function from SQL server)


Try CAST

Syntax CAST (value AS datatype)

Argument Description
val A column, constant, or expression; in SQL, val can also be a
host-language variable, function, or UDF
datatype Datatype to which to convert

Description CAST() allows mixing of numerics and characters in a single
expression by converting val to a specified datatype.

Normally, only similar datatypes can be compared in search conditions.
CAST() can be used in search conditions to translate one datatype into
another for comparison purposes.
Datatypes can be converted as shown in the following table:

TABLE 2 Compatible datatypes for CAST()

From datatype class To datatype class
Numeric  character, varying character, date, time, timestamp
Character, varying character numeric, date, time, timestamp
Date character, varying character, timestamp
Time character, varying character, timestamp
Timestamp character, varying character, date, time
Blob, arrays -

An error results if a given datatype cannot be converted into the datatype
specified in CAST().

Example In the following WHERE clause, CAST() is used to translate a
CHARACTER datatype, INTERVIEW_DATE, to a DATE datatype to compare against a
DATE
 datatype, HIRE_DATE:

. . .
 WHERE HIRE_DATE = CAST (INTERVIEW_DATE AS DATE);

See Also UPPER( )

Quote
"Wabbo" <funky_disco_fr...@yahoo.com> wrote in message

news:3c0f859e_2@dnews...
Quote
> Hi!
> Have to port one application from SQL server to Interbase. In SQL server
> they are using "TRANSFORM" function in SQL statement to get Crosstab
query.
> Any idea how to implement that in Interbase?
> Regards,
> FDF

Wabb

Delphi Developer
Tue, 25 May 2004 16:10:50 GMT

Re:How to simulate Crosstab query (TRANSFORM function from SQL server)


No this is not what I need. "Transform" is a word for crosstab query...it
has nothing with types and so on.
Try to imagine to build a query which will give you in rows States of
customers, in columns towns and the value will be how much customer is in
that town in that country. You don't know how much towns you have (column).
Something like Union query with known results (which is not in this case!)
Regards,
FDF

"savio" <sa...@nagem.com.br> schrieb im Newsbeitrag news:3c0fa20a_2@dnews...

Quote
> Try CAST

> Syntax CAST (value AS datatype)

> Argument Description
> val A column, constant, or expression; in SQL, val can also be a
> host-language variable, function, or UDF
> datatype Datatype to which to convert

> Description CAST() allows mixing of numerics and characters in a single
> expression by converting val to a specified datatype.

> Normally, only similar datatypes can be compared in search conditions.
> CAST() can be used in search conditions to translate one datatype into
> another for comparison purposes.
> Datatypes can be converted as shown in the following table:

> TABLE 2 Compatible datatypes for CAST()

> From datatype class To datatype class
> Numeric  character, varying character, date, time, timestamp
> Character, varying character numeric, date, time, timestamp
> Date character, varying character, timestamp
> Time character, varying character, timestamp
> Timestamp character, varying character, date, time
> Blob, arrays -

> An error results if a given datatype cannot be converted into the datatype
> specified in CAST().

> Example In the following WHERE clause, CAST() is used to translate a
> CHARACTER datatype, INTERVIEW_DATE, to a DATE datatype to compare against
a
> DATE
>  datatype, HIRE_DATE:

> . . .
>  WHERE HIRE_DATE = CAST (INTERVIEW_DATE AS DATE);

> See Also UPPER( )

> "Wabbo" <funky_disco_fr...@yahoo.com> wrote in message
> news:3c0f859e_2@dnews...
> > Hi!
> > Have to port one application from SQL server to Interbase. In SQL server
> > they are using "TRANSFORM" function in SQL statement to get Crosstab
> query.
> > Any idea how to implement that in Interbase?
> > Regards,
> > FDF

Brian

Delphi Developer
Sun, 30 May 2004 05:13:49 GMT

Re:How to simulate Crosstab query (TRANSFORM function from SQL server)


There is a book called "SQL for smarties" which has an example of how to
accomplish this.
I've never tried it myself but it may be worth a look.

Quote
"Wabbo" <funky_disco_fr...@yahoo.com> wrote in message

news:3c107988$1_1@dnews...
Quote
> No this is not what I need. "Transform" is a word for crosstab query...it
> has nothing with types and so on.
> Try to imagine to build a query which will give you in rows States of
> customers, in columns towns and the value will be how much customer is in
> that town in that country. You don't know how much towns you have
(column).
> Something like Union query with known results (which is not in this case!)
> Regards,
> FDF

> "savio" <sa...@nagem.com.br> schrieb im Newsbeitrag

news:3c0fa20a_2@dnews...
Quote
> > Try CAST

> > Syntax CAST (value AS datatype)

> > Argument Description
> > val A column, constant, or expression; in SQL, val can also be a
> > host-language variable, function, or UDF
> > datatype Datatype to which to convert

> > Description CAST() allows mixing of numerics and characters in a single
> > expression by converting val to a specified datatype.

> > Normally, only similar datatypes can be compared in search conditions.
> > CAST() can be used in search conditions to translate one datatype into
> > another for comparison purposes.
> > Datatypes can be converted as shown in the following table:

> > TABLE 2 Compatible datatypes for CAST()

> > From datatype class To datatype class
> > Numeric  character, varying character, date, time, timestamp
> > Character, varying character numeric, date, time, timestamp
> > Date character, varying character, timestamp
> > Time character, varying character, timestamp
> > Timestamp character, varying character, date, time
> > Blob, arrays -

> > An error results if a given datatype cannot be converted into the
datatype
> > specified in CAST().

> > Example In the following WHERE clause, CAST() is used to translate a
> > CHARACTER datatype, INTERVIEW_DATE, to a DATE datatype to compare
against
> a
> > DATE
> >  datatype, HIRE_DATE:

> > . . .
> >  WHERE HIRE_DATE = CAST (INTERVIEW_DATE AS DATE);

> > See Also UPPER( )

> > "Wabbo" <funky_disco_fr...@yahoo.com> wrote in message
> > news:3c0f859e_2@dnews...
> > > Hi!
> > > Have to port one application from SQL server to Interbase. In SQL
server
> > > they are using "TRANSFORM" function in SQL statement to get Crosstab
> > query.
> > > Any idea how to implement that in Interbase?
> > > Regards,
> > > FDF

Other Threads

1. How to simulate TRANSFORM function from SQL server (Crosstab query)

2. Sql Statement for Crosstab query

3. Crosstab Query in Delphi Sql

4. SQL query fails on SQL Server

5. coordinate transform function needed...

6. crosstab query in Interbase

7. How to create crosstab query?

8. How to create crosstab query in Delphi 4.0?

9. How to create simular Access crosstab query?

10. Delphi 4 and Crosstab Queries