Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

SQL Server 2005: CROSS APPLY

If you are using SQL 2005 and find User Defined Functions helpful, be sure to read my new article over at SQLTeam:

Returning Complex Data from User-Defined Functions with CROSS APPLY

SQL Server User-Defined Functions (UDFs) can return either a single value or virtual tables. However, sometimes we might like for a User-Defined Function to simply return more than 1 piece of information, but an entire table is more than what we need. For example, suppose we want a function that parses a single VARCHAR() containing a street address and returns:

  • Street Number
  • Street Name
  • Unit Number

In other words, it would accept "100 Main St #44" and return 3 distinct, separate values:

  • Street Number: "100"
  • Street Name: "Main St"
  • Unit Number: "#44"
As you can see, each value would have its own label and also potentially its own data type. Other examples would be a function that accepts an email address and returns the username and the domain separately, or a function that accepts a full name and parses it into separate First, Middle and Last values. In general, if a single function call can return multiple pieces of information all at once, it reduces the number of function calls you need to make, resulting in shorter code and allowing you to put more complex business logic into fewer functions.

Article link.

Stay tuned for more posts soon on CROSS APPLY and OUTER APPLY -- they are very useful features and can be really helpful if you need to transform or clean up denormalized (or just plain messy!) data using User Defined Functions.

see also:

Legacy Comments
SQL Server 2003: CROSS APPLY
I am facing with the problem of cross apply in created table. Actually, I want to exchange the data from column to row and row to column using SQL Server 2003. Is it possible to do this query in this database server? Kindly support me with suitable example.
Thank you.
Tshering Bhutia
Lab Instructor
Computer Science Deptt.
CCCT, Chisopani, South-Sikkim
Polytechnique College.

re: SQL Server 2005: CROSS APPLY

re: SQL Server 2005: CROSS APPLY
I have to transpose column in view

For me column is not Known previously so I cant hard code column in pivot are any existing method of transpose
If i use dynamic pivot . I am not able to use dynamic pivot in view
So i need solution to transpose column dynamically in view
With out hard coding column in query