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.
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:
- SQL Server 2005: CROSS APPLY
- Taking a look at CROSS APPLY
- SQL Server 2005: Specifying Partitions for Aggregate Functions
- SQL Server 2005: Using EXCEPT and INTERSECT to compare tables
- Regular Expression Replace in SQL 2005 (via the CLR)
- More with SQL Server 2005 : Top n Per Group, Paging, and Common Table Expressions
- SQL Server 2005: Using PARTITION and RANK in your criteria
- Lots of great SQL Server 2005 Express Downloads
Legacy Comments
|
tshering_983@yahoo.com
2008-04-08 |
SQL Server 2003: CROSS APPLY Sir, 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. |
|
w
2009-11-17 |
re: SQL Server 2005: CROSS APPLY d |
|
prem
2010-05-05 |
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 |