Viewing 15 posts - 54,601 through 54,615 (of 59,098 total)
Possibly, David... in most cases that I've come across like that, the multiple procs were actually written (initially) to support the natural RBAR that occurs in GUI related code... such...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2007 at 10:04 pm
Thanks Phil... guess I'll just keep doing CPR (Cut, Paste, 'n' Replace) using Word until they fix the forum code... I just copy from QA into Word and replace all...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2007 at 9:08 am
Jay H (10/16/2007)
And I apologize for not being more clear.
My primary key is a true primary key in another system. Instead of truncating the...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2007 at 9:03 am
They don't show if you copy and paste into Query Analyzer...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2007 at 9:02 am
Perfect... the rest of us can ignore you then.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2007 at 12:20 am
I can tell you haven't actually tried doing that... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2007 at 12:12 am
Then, this should do it... not sure WHY you want to do this to perfectly good data, but it will do it...
[font="Courier New"]--drop table yourtable, #Scratchpad
--===== Presets
    SET NOCOUNT ON
--===== Create a sample data table. THIS IS NOT PART OF THE SOLUTION
 CREATE TABLE yourtable (ID INT, Name VARCHAR(10),Title VARCHAR(15))
 INSERT INTO yourtable (ID, Name, Title)
 SELECT '1','Seba','Developer' UNION ALL
 SELECT '1','Joe','DBA' UNION ALL
 SELECT '1','Steve','Developer' UNION ALL
 SELECT '2','Deb','Developer' UNION ALL
 SELECT '3','Dave','DBA' UNION ALL
 SELECT '3','James','Developer' UNION ALL
 SELECT '3','Venkat','DBA' UNION ALL
 SELECT '3','Bob','Developer'
--===== Copy the data into a temp table with room for an additional column
 SELECT ID, Name, Title, CAST(0 AS INT) AS SecondKey
   INTO #Scratchpad
   FROM yourtable
--===== This index is an absolute must to get the grouped running count
     -- for the SecondKey column to work
 CREATE CLUSTERED INDEX Composite ON #ScratchPad (ID,Title,Name)
--===== Declare some obvious named variables
DECLARE @PrevID INT
    SET @PrevID = 0
DECLARE @SecondCount INT
DECLARE @SQL0 VARCHAR(8000)
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
--===== Create the grouped running count in the SecondKey column
 UPDATE #ScratchPad
    SET @SecondCount = SecondKey = CASE WHEN ID = @PrevID THEN @SecondCount+1 ELSE 1 END,
        @PrevID = ID
   FROM #ScratchPad WITH (INDEX(Composite),TABLOCKX)
--===== Create the necessary dynamic SQL
 SELECT @SQL0 = 'SELECT ID,'
 SELECT @SQL1 = ISNULL(@SQL1+',','')+CHAR(10) 
      + 'MAX(CASE WHEN SecondKey = ' + CAST(d.SecondKey AS VARCHAR(10)) + ' '
      + 'THEN Name  ELSE '''' END) AS Name'+ CAST(d.SecondKey AS VARCHAR(10)) + ','+CHAR(10)
      + 'MAX(CASE WHEN SecondKey = ' + CAST(d.SecondKey AS VARCHAR(10)) + ' '
      + 'THEN Title ELSE '''' END) AS Title' + CAST(d.SecondKey AS VARCHAR(10))
   FROM (SELECT DISTINCT SecondKey FROM #ScratchPad) d
  ORDER BY d.SecondKey
 SELECT @SQL2 = CHAR(10)+'FROM #ScratchPad GROUP BY ID'
--===== Execute the dynamic SQL
--PRINT @SQL0+@SQL1+@SQL2
   EXEC (@SQL0+@SQL1+@SQL2)[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2007 at 12:06 am
You called one of your columns "PK"... if it were trully a primary key, it would not allow the dupes.
Doesn't matter... the code I posted will find them as you...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 11:18 pm
What you're calling a "PK" obviously isn't where this table is concerned or you wouldn't be able to insert the duplicate rows.
However, to solve your problem, this will work... and,...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 9:21 pm
Please see the following... leaves no quotes to cleanup...
http://www.sqlservercentral.com/Forums/Topic296166-8-1.aspx#BM296961
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 8:52 pm
Phil,
What product are you using to format the code so nicely?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 8:39 pm
Just curious... why do you have the same ID for 3 different people?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 6:14 pm
Whew! Yeah, I knew the *= and =* outer joins would sometimes cough up bad results... they actually started going bad in SQL Server 7... I'm surprised they didn't...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 5:22 pm
Lowell (10/15/2007)
the DBA toolkit here on SSC has regular expressions as extended stored procedures for SQL2K; i use it all the time.
Thanks for the tip, Lowell...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 5:07 pm
Correct... and it's not a good idea to base things on the system clock... SQL Server only has a resolution of 3.3 milliseconds... a lot can happen in that...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 8:14 am
Viewing 15 posts - 54,601 through 54,615 (of 59,098 total)