Viewing 15 posts - 54,571 through 54,585 (of 59,067 total)
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...
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...
October 16, 2007 at 9:03 am
They don't show if you copy and paste into Query Analyzer...
October 16, 2007 at 9:02 am
Perfect... the rest of us can ignore you then.
October 16, 2007 at 12:20 am
I can tell you haven't actually tried doing that... 😉
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]
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...
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,...
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
October 15, 2007 at 8:52 pm
Phil,
What product are you using to format the code so nicely?
October 15, 2007 at 8:39 pm
Just curious... why do you have the same ID for 3 different people?
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...
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...
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...
October 15, 2007 at 8:14 am
You've seen simple equi-joins go bad? Any chance you have an example of one that has gone bad?
October 15, 2007 at 8:07 am
Viewing 15 posts - 54,571 through 54,585 (of 59,067 total)