Refresh column for different aggregate values

Question

I am creating a script that is to "merge" and remove duplicate rows from the table. The table contains address information and uses a whole field to store information about email in the form of bit flags (column name lngValue). For example, lngValue and 1 == 1 means its primary address.

There are cases when the same letter is entered twice, but sometimes with different lngValues. To solve this problem, I need to take lngValue from all duplicates and assign them to one saved record and delete the rest.

My biggest headache, as far as it was with the "merging" of records. What I want to do is bitwise or all lngValues ​​of duplicate entries together. Here is what I still have, which only finds the value of all lngValues ​​bitwise or together.

Warning: confusing code ahead

declare @duplicates table
(
lngInternetPK int,
lngContactFK int,
lngValue int
)

insert into @duplicates (lngInternetPK, lngContactFK, lngValue) 
(
select  tblminternet.lngInternetPK, tblminternet.lngContactFK, tblminternet.lngValue   from tblminternet  inner join 
(select strAddress, lngcontactfk, count(*) as count from tblminternet where lngValue & 256 <> 256 group by strAddress, lngcontactfk) secondemail
On tblminternet.strAddress = secondemail.strAddress and
tblminternet.lngcontactfk = secondemail.lngcontactfk 
where count > 1 and tblminternet.strAddress is not null and tblminternet.lngValue & 256 <> 256 --order by lngContactFK, strAddress
)

update @duplicates set lngValue = t.val

from 
                (select (sum(dupes.lngValue) & 65535) as val from 
                    (select  here.lngInternetPK,                     here.lngContactFK, here.lngValue from tblminternet here  inner join 
                    (select strAddress, lngcontactfk, count(*) as count from tblminternet where lngValue & 256 <> 256 group by strAddress, lngcontactfk) secondemail
                    On here.strAddress = secondemail.strAddress     and
                    here.lngcontactfk = secondemail.lngcontactfk 
                    where count > 1 and here.strAddress is not      null and here.lngValue & 256 <> 256) dupes, tblminternet this

                where this.lngContactFK = dupes.lngContactFK
                ) t
where lngInternetPK in (select lngInternetPK from @duplicates)    

Edit:
As requested here are some sample data:

Table Name: tblminternet Column
Names:
lngInternetPK
lngContactFK
lngValue
strAddress

Example line 1:
lngInternetPK: 1
lngContactFK: 1
lngValue: 33
strAddress: " [e-mail] "

2:
lngInternetPK: 2
lngContactFK: 1
lngValue: 40
strAddress: "[e-mail]"

, :
lngInternetPK: 1
lngContactFK: 1
lngValue: 41
strAddress: "[e-mail]"

:
, ( ).

Answer 1

SQL Server , .

, 0 15, MAX ( OR ), SUM ( -).

lngInternetPK (lngContactFK, strValue) lngValue .

;WITH   bits AS
        (
        SELECT  0 AS b
        UNION ALL
        SELECT  b + 1
        FROM    bits
        WHERE   b < 15
        ),
        v AS
        (
        SELECT  i.*,
                (
                SELECT  SUM(value)
                FROM    (
                        SELECT  MAX(lngValue & POWER(2, b)) AS value
                        FROM    tblmInternet ii
                        CROSS JOIN
                                bits
                        WHERE   ii.lngContactFK = i.lngContactFK
                                AND ii.strAddress = i.strAddress
                        GROUP BY
                                b
                        ) q
                ) AS lngNewValue
        FROM    (
                SELECT  ii.*, ROW_NUMBER() OVER (PARTITION BY lngContactFK, strAddress ORDER BY lngInternetPK) AS rn
                FROM    tblmInternet ii
                ) i
        WHERE   rn = 1
        )
UPDATE  v
SET     lngValue = lngNewValue;

;WITH    v AS
        (
        SELECT  ii.*, ROW_NUMBER() OVER (PARTITION BY lngContactFK, strAddress ORDER BY lngInternetPK) AS rn
        FROM    tblmInternet ii
        )
DELETE  v
WHERE   rn > 1

. :

• SQL Server:

Answer 2

, , . . , . , .

Declare @tblminternet 
Table 
( lngInternetPK int,   
  lngContactFK int,  
  lngValue int, 
  strAddress varchar(255)
)

Insert Into @tblminternet 
select 1, 1, 33, '[e-mail]' 
union
select 2, 1, 40, '[e-mail]'
union 
select 3, 2, 33, '[e-mail]'
union 
select 4, 2, 40, '[e-mail]'
union 
select 5, 3, 2, '[e-mail]'

--Select * from @tblminternet

Select  Distinct   
    A.lngContactFK , 
    A.lngValue | B.lngValue as 'Bitwise OR', 
    A.strAddress
From @tblminternet A, @tblminternet B
Where A.lngContactFK = B.lngContactFK
And A.strAddress = B.strAddress
And A.lngInternetPK != B.lngInternetPK

Answer 3

SQL Server .NET, SQL. , SQL Server 2005 Visual Studio 2010. Visual Studio 2013 Community Edition ( ) .NET 2 SQL Server 2005.

. MSDN: https://msdn.microsoft.com/en-us/library/91e6taax(v=vs.90).aspx

CLR SQL-: https://msdn.microsoft.com/en-us/library/ms131048.aspx

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

• SQL Server → SQL Server
• ""
• SQL Server " "
• CLR SQL CLR (, VB)
• → ...
• , SQL Server → SQL CLR VB → SQL CLR VB Aggregate

VB:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server


<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)> _
Public Structure AggregateBitwiseOR

    Private CurrentAggregate As SqlTypes.SqlInt32

    Public Sub Init()
        CurrentAggregate = 0
    End Sub

    Public Sub Accumulate(ByVal value As SqlTypes.SqlInt32)
        'Perform Bitwise OR against aggregate memory
        CurrentAggregate = CurrentAggregate OR value
    End Sub

    Public Sub Merge(ByVal value as AggregateBitwiseOR)
        Accumulate(value.Terminate())
    End Sub

    Public Function Terminate() As SqlInt32
        Return CurrentAggregate
    End Function

End Structure

: https://msdn.microsoft.com/en-us/library/dahcx0ww(v=vs.90).aspx

• , : Build → Build ProjectName ( 04018, @http://msdn.microsoft.com/en-US/data/hh297027 : → , Microsoft SQL Server Update )
• DLL C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn C:\

• DLL:

  CREATE ASSEMBLY [CLRTools] FROM 'c: CLRTools.dll WITH PERMISSION_SET = SAFE

• SQL:

  CREATE AGGREGATE [dbo]. [AggregateBitwiseOR] ( @INT)    INT   EXTERNAL NAME [CLRTools]. [CLRTools.AggregateBitwiseOR];

If you get the "Invalid syntax next to" EXTERNAL "error message, then change the database compatibility level using the following commands:

For SQL Server 2005: EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008: EXEC sp_dbcmptlevel 'DatabaseName', 100

5. Check your code:

   SELECT dbo.AggregateBitwiseOR (Foo) AS Foo FROM Bar

I found this article useful: http://www.codeproject.com/Articles/37377/SQL-Server-CLR-Functions