x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

February 2005 Blog Posts

New twist on Delimited string per ID using many columns...AND this time into a table

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...

posted @ Wednesday, February 23, 2005 3:30 PM | Feedback (0) | Filed Under [ SQL Server ]

Show me The TOP n Number of things based on a key

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...

posted @ Thursday, February 10, 2005 10:02 AM | Feedback (3) | Filed Under [ SQL Server ]

How do I search a database for the umpteenth time

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...

posted @ Tuesday, February 08, 2005 1:20 PM | Feedback (4) | Filed Under [ SQL Server ]

Mimic Oracles INSTR Function

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...

posted @ Wednesday, February 02, 2005 1:03 PM | Feedback (11) | Filed Under [ SQL Server ]

Powered by:
Powered By Subtext Powered By ASP.NET