February 2005 Blog Posts
OK...this is out there..based on this previous thread that I got a LOT of help on, SQL Team was asked a Question from MikeB to basically take a columns “key“ and marry a set of columns and place them all on 1 row, as a result set, or in to a table.
The correct answer is that this is a presentation issue, but I couldn't help myself. Basically take this:
fkItemID fkCostID Rate Units -------- -------- --------- ---------- 1 21 45.0000 DAY1 23 400.0000 LSUM2 22 225.0000 DAY
And Produce this:
fkItemID fkCostID1 Rate1 Units1 fkCostID2 Rate2 Units2 -------- --------- -------- ------ --------- --------- ------ 1 21 45.0000 DAY 23 400.0000 LSUM2 22 225.0000 DAY NULL NULL NULL
Here's the code. Just make sure to add the password for the bcp statements, or...
OK, another one that seems to be often asked, and I'm sure there's a SQL Team article out there...I'm just lazy. Anyway, this post got me interested in this again (and I always have trouble trying to remember the answer), so I decided to post some solutions, so I don't have to remember. With the Help of Pat Phelan, Rudy Limeback, and Mr. B. Lindman (aka the Blind Dude), we came up with the following.
Notice the differences in the Plans. While Pat's Subquery looks cleaner, Rudy's Join seems more effecient. Any comments on the plans would be greatly appreciated.
EDIT: Sunsande points...
OK, so I got tired of writing this...so I decided to build a bells and whistles full database search. Now at first blush, the reaction that this is bad....and it is...but I've been handed so many poorly designed databases, I felt for this persons post. So now I'm done...I don't have to write it again....I guess we could expanded it to server the entire server....
EDIT: Modified to Search Text and ntext as well. Changes are highlighted in Red. Anyone know why the Row counts are still being produced?
USE NorthwindGO
SET NOCOUNT OFFCREATE TABLE myTableSearch99( SPID int , SearchDate datetime , TABLE_CATALOG sysname , TABLE_SCHEMA...
Starting at a user defined location in a string to find the nth occurance of a target string's starting location
EDIT: OK, I got Jay's set based method to work. And I mistook Jay for Jay White (Page47) but I was wrong (but I should be given a pass, because it's just like what Mr. 47 would have written). Now we have to ask, Jay who?
Not anymore, thanks Jay
Anyway Thanks Jay. Just as a note: This requires a numbers table. The concept of using this to eliminate recursion and cursors is sort of akin to turning a light on after stumbling...