Create a group where either value from two columns for a row matches either value from two columns for a different row

Question

I am trying to create a Group ID where either value from two columns in a table matches the either value from the two columns for a different row. For example:

Table:

+---------+-------------------+
| ogr_fid | comapared_ogr_fid |
+---------+-------------------+
|       8 |                10 |
|       6 |                 8 |
|      10 |                 4 |
|       5 |                 3 |
|       5 |                 2 |
|      12 |                15 |
+---------+-------------------+

Ideal Output:

+---------+-------------------+-------+
| ogr_fid | comapared_ogr_fid | SP_ID |
+---------+-------------------+-------+
|       8 |                10 |     1 |
|       6 |                 8 |     1 |
|      10 |                 4 |     1 |
|       5 |                 3 |     2 |
|       5 |                 2 |     2 |
|      12 |                15 |     3 |
+---------+-------------------+-------+

Any thoughts on how I could accomplish this? I've been trying to use a cursor. Below is my code. It seems to work, but returns duplicates. The commented out code does not work, it returns the error:

Warning: Null value is eliminated by an aggregate or other SET operation.

IF OBJECT_ID('SpatialAggregation.dbo.zzzInitialStack') IS NOT NULL -- Drop temp table if exists
    DROP TABLE dbo.zzzInitialStack;
GO

CREATE TABLE dbo.zzzInitialStack
    (
        SP_ID int,
        ogr_fid int,
        compared_ogr_fid int
    )
GO

IF OBJECT_ID('tempdb.dbo.#Stack') IS NOT NULL
    DROP TABLE #Stack
;

SELECT ogr_fid
      ,compared_ogr_fid
    INTO #Stack
    FROM dbo.zzzCentroidCalc
    WHERE CentroidDistance < 20 AND AvgScore >= 0.70 AND ogr_fid IS NOT NULL AND compared_ogr_fid IS NOT NULL

-- Declare Initial Variables
DECLARE @ogr_fid int, @compared_ogr_fid int, @sp_id int, @sp int, @in_grp int 

-- set SP ID group to 1
SET @sp_id = 1


-- create Cursor
DECLARE sp_cursor CURSOR FOR
SELECT ogr_fid
       ,compared_ogr_fid
    FROM #Stack
    ORDER BY ogr_fid, compared_ogr_fid 

OPEN sp_cursor

--Select next row from cursor
FETCH NEXT FROM sp_cursor
INTO @ogr_fid, @compared_ogr_fid

WHILE @@FETCH_STATUS = 0 
--Start Stacked Parcel grouping
BEGIN
    --IF (SELECT COUNT(*) FROM dbo.zzzInitialStack) IS NULL
    --  BEGIN
    --  INSERT INTO dbo.zzzInitialStack (SP_ID, ogr_fid,compared_ogr_fid)
    --      VALUES(@sp_id,@ogr_fid,@compare_ogr_fid)
    --  SET @sp_id = @sp_id + 1
    --  END
    --ELSE IF (SELECT count(*) FROM dbo.zzzInitialStack WHERE @ogr_fid = ogr_fid) IS NOT NULL 
    --  BEGIN
    --  SET @sp = (SELECT MAX(SP_ID) FROM dbo.zzzInitialStack WHERE @ogr_fid = ogr_fid)
    --  INSERT INTO dbo.zzzInitialStack(SP_ID, ogr_fid,compared_ogr_fid)
    --      VALUES(@sp,@ogr_fid,@compare_ogr_fid)
 --     END
    --ELSE IF (SELECT COUNT(*) FROM dbo.zzzInitialStack WHERE @compared_ogr_fid = ogr_fid) IS NOT NULL
    --  BEGIN
    --    SET @sp = (SELECT MAX(SP_ID) FROM dbo.zzzInitialStack WHERE @compared_ogr_fid = ogr_fid)
    --  INSERT INTO dbo.zzzInitialStack(SP_ID, ogr_fid,compared_ogr_fid)
    --      VALUES(@sp,@ogr_fid,@compared_ogr_fid)
    --  END
    --ELSE
    --  BEGIN
    --    INSERT INTO dbo.zzzInitialStack(SP_ID, ogr_fid, compared_ogr_fid)
    --      VALUES(@sp_id,@ogr_fid,@compared_ogr_fid)
    --  SET @sp_id = @sp_id + 1
    --  END

    INSERT INTO dbo.zzzInitialStack (SP_ID,ogr_fid,compared_ogr_fid)
    SELECT CASE WHEN @ogr_fid IN (SELECT ogr_fid FROM dbo.zzzInitialStack) OR @ogr_fid IN (SELECT ogr_fid FROM dbo.zzzInitialStack)
                    OR @compared_ogr_fid IN (SELECT ogr_fid FROM dbo.zzzInitialStack) OR @compared_ogr_fid IN (SELECT ogr_fid FROM dbo.zzzInitialStack) 
                THEN (SELECT TOP 1 SP_ID FROM dbo.zzzInitialStack
                            WHERE @ogr_fid = ogr_fid OR @ogr_fid = compared_ogr_fid
                                  OR @compared_ogr_fid = ogr_fid OR @compared_ogr_fid = compared_ogr_fid) 
                ELSE (SELECT CASE WHEN MAX(SP_ID) IS NULL 
                                  THEN 1
                                  ELSE CAST(MAX(SP_ID) + 1 AS INT)
                                  END
                            FROM dbo.zzzInitialStack) 
            END AS SP_ID
          ,@ogr_fid AS ogr_fid
          ,@compared_ogr_fid AS compared_ogr_fid

FETCH NEXT FROM sp_cursor INTO @ogr_fid, @compared_ogr_fid
END;

CLOSE sp_cursor;
DEALLOCATE sp_cursor;
GO

IF OBJECT_ID('dbo.StackedParcels') IS NOT NULL
    DROP TABLE dbo.StackedParcels;

WITH cte1 AS (
    SELECT DISTINCT SP_ID, ogr_fid
        FROM dbo.zzzInitialStack
  UNION ALL
    SELECT DISTINCT SP_ID, compared_ogr_fid AS ogr_fid
        FROM dbo.zzzInitialStack
             )
,cte2 AS (
    SELECT SP_ID, count(ogr_fid) AS NumParcels
      FROM cte1 
      GROUP BY SP_ID
         )
SELECT a.SP_ID
      ,b.ogr_fid
      ,a.NumParcels
    INTO dbo.StackedParcels
    FROM cte2 AS a JOIN cte1 AS b ON a.SP_ID = b.SP_ID

SP_ID is just a random unique identifier for a grouping. I was attempting to set the value for each new grouping to be one greater than that of any previously identified grouping. — bjurstrs, Feb 09 '16 at 18:05
what version of SQL Server are you using? i.e sql server 2012 and above or SQL Server 2008R2 and below? — Fuzzy, Feb 09 '16 at 18:08
I'm still trying to get my head around how you want them grouped by. Can you explain the grouping logic again? — Fuzzy, Feb 09 '16 at 18:12
I would like to form groups around matching a row's ogr_fid or compared_ogr_fid to another row's ogr_fid or compared_ogr_fid. — bjurstrs, Feb 09 '16 at 18:51

score 1 · Accepted Answer · edited May 23 '17 at 11:52

Strangely I've only recently solved a very similar problem. So I basically used the solution I gave there, tweaked to your data fields.

Note that this script doesn't handle NULL values. If you have NULL values in your source table, replace them with the minimum value for INT (-2147483648) before generating the set IDs. Replace them with NULL in the final SELECT statement using NULLIF.

SET NOCOUNT ON;
CREATE TABLE #id(ID INT IDENTITY(1,1) PRIMARY KEY,ogr_fid INT,comapared_ogr_fid INT);
INSERT INTO #id(ogr_fid,comapared_ogr_fid) VALUES
    (8,10),
    (6,8),
    (10,4),
    (5,3),
    (5,2),
    (12,15);

CREATE TABLE #sets(set_id INT,fid INT PRIMARY KEY);

DECLARE c_id CURSOR FAST_FORWARD FOR
    SELECT ogr_fid,comapared_ogr_fid FROM #id;

OPEN c_id;
DECLARE @ogr_fid INT;
DECLARE @comapared_ogr_fid INT;
DECLARE @last_created_set_id INT=0;
WHILE 1=1
BEGIN
    FETCH NEXT FROM c_id INTO @ogr_fid,@comapared_ogr_fid;
    IF @@FETCH_STATUS<>0 BREAK;

    DECLARE @set_id_1 INT=(SELECT set_id FROM #sets WHERE fid=@ogr_fid);
    DECLARE @set_id_2 INT=(SELECT set_id FROM #sets WHERE fid=@comapared_ogr_fid);

    IF @set_id_1 IS NOT NULL AND @set_id_2 IS NOT NULL 
        CONTINUE;
    ELSE IF @set_id_1 IS NOT NULL
        INSERT INTO #sets(set_id,fid)VALUES(@set_id_1,@comapared_ogr_fid);
    ELSE IF @set_id_2 IS NOT NULL
        INSERT INTO #sets(set_id,fid)VALUES(@set_id_2,@ogr_fid);
    ELSE
    BEGIN
        DECLARE @id_min INT=CASE WHEN @ogr_fid<@comapared_ogr_fid THEN @ogr_fid ELSE @comapared_ogr_fid END;
        DECLARE @id_check INT;
        WHILE 1=1
        BEGIN
            SET @id_check=(
                SELECT MIN(identifier)
                FROM (
                    SELECT MIN(ogr_fid) FROM #id WHERE comapared_ogr_fid=@id_min AND ogr_fid<@id_min HAVING MIN(ogr_fid) IS NOT NULL
                    UNION ALL
                    SELECT MIN(comapared_ogr_fid) FROM #id WHERE ogr_fid=@id_min AND comapared_ogr_fid<@id_min HAVING MIN(comapared_ogr_fid) IS NOT NULL
                ) AS lu(identifier)
            );
            IF @id_check IS NULL
                BREAK;
            SET @id_min=@id_check;
        END

        DECLARE @set_id_min INT=(SELECT set_id FROM #sets WHERE fid=@id_min);
        IF @set_id_min IS NULL
        BEGIN
            SET @last_created_set_id=@last_created_set_id+1;
            SET @set_id_min=@last_created_set_id;
            INSERT INTO #sets(set_id,fid)VALUES(@set_id_min,@id_min);
        END
        IF @ogr_fid<>@id_min
            INSERT INTO #sets(set_id,fid)VALUES(@set_id_min,@ogr_fid);
        IF @comapared_ogr_fid NOT IN (@ogr_fid,@id_min)
            INSERT INTO #sets(set_id,fid)VALUES(@set_id_min,@comapared_ogr_fid);
    END
END
CLOSE c_id;
DEALLOCATE c_id;

SELECT 
    id.ogr_fid,
    id.comapared_ogr_fid,
    s.set_id AS Gr_ID 
FROM 
    #id AS id
    INNER JOIN #sets AS s ON
        s.fid=id.ogr_fid
ORDER BY 
    s.set_id,
    id.ID;

DROP TABLE #sets;
DROP TABLE #id;

Results:

+---------+-------------------+-------+
| ogr_fid | comapared_ogr_fid | SP_ID |
+---------+-------------------+-------+
|       8 |                10 |     1 |
|       6 |                 8 |     1 |
|      10 |                 4 |     1 |
|       5 |                 3 |     2 |
|       5 |                 2 |     2 |
|      12 |                15 |     3 |
+---------+-------------------+-------+

Create a group where either value from two columns for a row matches either value from two columns for a different row

1 Answers1