question

Can you post the table definitions? I have a solution which would be reasonably quick, but it needs either a suitable clustered index, or there to be another column in the table which can uniquely identify each entry.

If you can edit your question to include that, then I will post you a solution.

A couple of issues you'll need to address:

1. You're not specifying a unique ordering. Why should the rows be ordered the way they are (within each sub-class of being the same as far as the combine field goes)?
2. Do you need to store the duplicate information that's present in the Combine field? If you need this for the application, I would suggest generally calculating that in a stored procedure, or a view.

Similarly, you will be able to get the dupe out by running a select along the lines of:

SELECT Ldg, Bnk, Combine, Dupe, ROW_NUMBER() OVER (PARTITION BY Combine ORDER BY Bnk)
FROM MyTable
ORDER BY Combine

But this is still going to struggle to be useful without a unique ordering.

Folks, I appreciate your help ,Unfortunately I have no control over the data I import, It is from an external source and what I see is what I get.

I'm new to SQL so I'm still in the dark a lot about what I can and cannot do. I just taught that if it could be done in VB there was a chance it could be done in SQL. But I will keep trying. Thank you All

Matt ,
Thanks for the mail. this is my table,

1 LDG varchar 50 1
0 BNK decimal 9 1
0 COMBINE varchar 50 1
0 DUPE numeric 9 1
0 UniqueID varchar 50 1
0 id int 4 1

I have got a little further with my problem.
The ID field in table is not used and can be changed to any field type if required.

I use the following SQL to create the data the way I require it. Then I run the the query
to file and use DTS to import it into a temp table and use sql to transfer the required data into TBlCombine. I know it's not a perfect answer but it's gets the job done. But it would be better just to run an update process of some sort. I don't have the skill as yet to modify this SQl to change it to an update process.

DECLARE @Test table (Ldg varCHAR(50),

Bnk decimal(9,2),

Combine varchar(50),

Dupe int IDENTITY(1,1))

INSERT INTO @Test (Ldg, Bnk, Combine)

SELECT Ldg, Bnk, Ldg+CAST(Bnk as varchar(25))

FROM TblCombine ORDER BY TblCombine.LDG , TblCombine.BNK , TblCombine.COMBINE

SELECT rtrim(TblCombine.LDG) as Ldg, TblCombine.BNK, rtrim(TblCombine.LDG) + rtrim(cast(TblCombine.BNK as varchar(50))) as Combine , test.Dupe - TblCombine.Dupe + 1 AS Dupe

FROM @Test test

INNER JOIN (SELECT Ldg, Bnk, MIN(Dupe) AS Dupe

        FROM @Test
        GROUP BY Ldg, Bnk)Tblcombine

on Test.Ldg = TblCombine.Ldg where test.Bnk = TblCombine.Bnk

Data after SQl runs,

ldg bnk combine Dupe

BQ 75 BQ75.00 1
BQ 85 BQ85.00 1
BQ 85 BQ85.00 2
BQ 85 BQ85.00 3
BQ 95 BQ95.00 1
INT 95 INT95.00 1
INT 95 INT95.00 2
INT 95 INT95.00 3
INT 105 INT105.00 1
INT 105 INT105.00 2
INT 106 INT106.00 1
INT 107 INT107.00 1

AS you can see the number in the Dupe field is based on the combine field, which increases by 1 each time the field is the same.

Any further help would be great.

Regards

Pator

Pator,

I realize this is a pretty old post... do you still need an answer on this because, if you do, I have one. I just don't want to take the time to write it up if you don't need it.

Jeff I would be interested in a SQl solution, I did get around the problem by exporting the sql query as a text file and then re-import into the database. I know it's not a perfect solution but it got the job done. However a SQl solution would still be very benificial. Regards & Thanks Pator

Oh, sorry... I'm not sure how I lost track of this one so very badly.

Pator, here's the SQL Server 2000 method I was thinking off. The clustered index, TABLOCKX, and MAXDOP thingies are absolutely essential...

--===== Create and populate a test table.
     -- This is NOT a part of the solution
 CREATE TABLE #TblCombine
        (
        Ldg     VARCHAR(5),
        Bnk     DECIMAL(9,2),
        Combine VARCHAR(20),
        Dupe    INT
        )

 INSERT INTO #TblCombine
        (Ldg,Bnk,Combine,Dupe)
 SELECT 'BQ','55.23','BQ55.23','0' UNION ALL
 SELECT 'BQ','65.23','BQ65.23','0' UNION ALL
 SELECT 'BQ','75.23','BQ75.23','0' UNION ALL
 SELECT 'BQ','85.23','BQ85.23','0' UNION ALL
 SELECT 'BQ','85.23','BQ85.23','0' UNION ALL
 SELECT 'BQ','85.23','BQ85.23','0' UNION ALL
 SELECT 'Int','95.23','Int95.23','0' UNION ALL
 SELECT 'Int','105.23','Int105.23','0' UNION ALL
 SELECT 'Int','95.23','Int95.23','0' UNION ALL
 SELECT 'Int','95.23','Int95.23','0'

--===================================================================
--      Solution starts here
--===================================================================
--===== If you don't have THIS index on the table, then it won't work
 CREATE CLUSTERED INDEX IX_TblCombine_CoverUpdate
     ON #TblCombine (Combine)

--===== Create a couple of necessary obviously named variables
DECLARE @PrevDupe    INT,
        @PrevCombine VARCHAR(20)

--===== Update the dupe column
 UPDATE #TblCombine
    SET @PrevDupe    = Dupe = CASE WHEN Combine = @PrevCombine THEN @PrevDupe+1 ELSE 1  END,
        @PrevCombine = Combine
   FROM #TblCombine WITH(TABLOCKX) --Absolutely essential.  DO NOT REMOVE
 OPTION (MAXDOP 1) --Absolutely essential.  DO NOT REMOVE

--===== Show the result
 SELECT * FROM #TblCombine ORDER BY Combine,Dupe