How to create multiple rows combinations based on one COLUMN

Question

I have a SQL Table with the following data

Creating table:

create TABLE TEMP_RIGHT_ROLLUP_GROUP (
ASSETCODE VARCHAR(20),
RIGHTNAME VARCHAR(50),
ACQUISITIONBEGINDATE TIMESTAMP,
ACQUISITIONENDDATE TIMESTAMP
);

Data Ingestion:

insert into TEMP_RIGHT_ROLLUP_GROUP VALUES 
('10','R1','2023-01-01 00:00:00.000','2023-10-31 00:00:00.000'),
('10','R1','2024-04-01 00:00:00.000','2026-12-31 00:00:00.000'),
('10','R2','2023-02-01 00:00:00.000','2027-12-31 00:00:00.000'),
('10','R3','2023-02-01 00:00:00.000','2024-07-31 00:00:00.000'),
('10','R3','2024-10-01 00:00:00.000','2026-12-31 00:00:00.000');

The data looks like this below:

Need to create output by multiplying RightName with others RightName

Tried below code:

with cte_multiple_window as (
    select
      A.*,
      count(*) over (partition by assetcode,rightname) cnt
    from TEMP_RIGHT_ROLLUP_GROUP A
),
cte_denorm as (
    select
      A.assetcode assetcode_mw,
      A.rightname rightname_mw,
      A.ACQUISITIONBEGINDATE ACQUISITIONBEGINDATE_mw,
      A.ACQUISITIONENDDATE ACQUISITIONENDDATE_mw,
      B.assetcode assetcode_2,
      B.rightname rightname_2,
      B.ACQUISITIONBEGINDATE ACQUISITIONBEGINDATE_2,
      B.ACQUISITIONENDDATE ACQUISITIONENDDATE_2
    from cte_multiple_window A
    inner join cte_multiple_window B on A.assetcode = B.assetcode and A.RIGHTNAME <> B.rightname
    where A.cnt > 1 
      and B.cnt = 1
    order by
      A.assetcode,
      A.ACQUISITIONBEGINDATE,
      A.ACQUISITIONENDDATE
)
-- select * from cte_denorm;
,
cte_norm as (
    select distinct
      A.assetcode_mw,
      A.rightname_mw,
      A.ACQUISITIONBEGINDATE_mw,
      A.ACQUISITIONENDDATE_mw,
      B.*
    from cte_denorm A
    inner join cte_multiple_window B on (
      (A.assetcode_mw = B.assetcode and
       A.rightname_mw = B.RIGHTNAME and
       A.ACQUISITIONBEGINDATE_mw = B.ACQUISITIONBEGINDATE and
       A.ACQUISITIONENDDATE_mw=B.ACQUISITIONENDDATE
      ) or
      (A.assetcode_2 = B.assetcode and
       A.rightname_2 = B.RIGHTNAME and
       A.ACQUISITIONBEGINDATE_2 = B.ACQUISITIONBEGINDATE and
       A.ACQUISITIONENDDATE_2 = B.ACQUISITIONENDDATE
      ))
    order by
      A.assetcode_mw,
      A.rightname_mw,
      A.ACQUISITIONBEGINDATE_mw,
      A.ACQUISITIONENDDATE_mw
)
select *
from cte_norm;

Tried different ways like cross join, self join, CTE and row_number nothing is working, can you guys help me to generate this output?

The question is simple. Let's say we have the following data:

R1   R1   R2   R3   R3

If there are any repeated rights, then we need to create all possible combinations like the below:

R1_1   R2   R3_1

R1_1   R2   R3_2

R1_2   R2   R3_1

R1_2   R2   R3_2

Expected SQL output:

Answer 1

OK, it's not as efficient as it might be and definitely could use some cleanup, but it works. I might come back and post a second solution using set based logic instead of a cursor, but the cursor based solution works and is (relative to a set based solution) easy to understand.

The basic principle is to find a set of "RightName" values and find how many alternatives there are for each. Once you have that, build an output set that has that many copies of the list of names, and then iterate over that set to "count up" (use successively higher alternatives to the first of each RightName) as you increase the "Output Group" you're working with.

Anyway, here is the code, and sample output is below that, good luck and report back on how it goes!

SELECT OutIDLog, CONVERT(nvarchar(4000), LogValue) as LogValue 
INTO #Log FROM (VALUES (0, 'Log table')) as LogTbl(OutIDLog, LogValue);

WITH cteSample as (
    SELECT * FROM (VALUES 
        (CONVERT(nvarchar(20), '10'),CONVERT(nvarchar(50),'R1')
            ,CONVERT(datetime2(0),'2023-01-01 00:00:00.000')
            ,CONVERT(datetime2(0),'2023-10-31 00:00:00.000')),
        ('10','R1','2024-04-01 00:00:00.000','2026-12-31 00:00:00.000'),
        ('10','R2','2023-02-01 00:00:00.000','2027-12-31 00:00:00.000'),
        ('10','R3','2023-02-01 00:00:00.000','2024-07-31 00:00:00.000'),
        ('10','R3','2024-10-01 00:00:00.000','2026-12-31 00:00:00.000'),
        ('10','R4','2022-01-01 00:00:00.000','2026-12-31 00:00:00.000')
        ) as SampleTable(ASSETCODE, RIGHTNAME, ACQUISITIONBEGINDATE, ACQUISITIONENDDATE)
), cteIDs as (
    SELECT *
        , RIGHTNAME as RNBase
        , ROW_NUMBER() OVER (ORDER BY RIGHTNAME, ACQUISITIONBEGINDATE) -1 as IDOverAll
        , ROW_NUMBER() OVER (PARTITION BY RIGHTNAME ORDER BY ACQUISITIONBEGINDATE) -1 as IDInGroup
    FROM cteSample as S
) SELECT * 
INTO #DataWithIDs
FROM cteIDs;

WITH cteBaseRNs as (
    SELECT RNBase, COUNT(*) as GroupSize
        , ROW_NUMBER() OVER (ORDER BY RNBase) as IDOfGroup
    FROM #DataWithIDs as S
    GROUP BY RNBase
), cteExp as (
    SELECT I.*, S.GroupSize, S.IDOfGroup
        , CONCAT (I.RNBase, '_', CONVERT(nvarchar(5), IDInGroup)) as RNExt
    FROM cteBaseRNs as S INNER JOIN #DataWithIDs as I on I.RNBase = S.RNBase 
), cteTally as (
    SELECT IDOverAll as Tally FROM cteExp
), cteOutGroups as (
    SELECT row_number() over (ORDER BY T.Tally, IDOfGroup) as OutID
        , R.*, 0 as IDInGroup --Zero based ordinal to distinguish between duplicate base names
        , CASE WHEN IDOfGroup = 1 THEN T.Tally ELSE 0 END as ToAdd
        , 0 as Remainder, T.Tally + 1 as OutGroup
    FROM cteTally as T CROSS JOIN cteBaseRNs as R
    WHERE T.Tally < (SELECT exp(sum(log(GroupSize))) FROM cteBaseRNs)
)SELECT * 
INTO #OutTable
FROM cteOutGroups as O
ORDER BY OutGroup, IDOfGroup

SELECT * FROM #DataWithIDs
SELECT * FROM #OutTable

--
DECLARE --Working variables
    @Carry INT
    , @TempAdd INT
    , @LastID INT
    , @NewID INT
    , @LastOutID INT
    , @TempInt INT;

DECLARE --Cursor variables
    @OutGroup INT,
    @OutID INT, 
    @GroupSize INT,
    @IDInGroup INT,
    @IDOfGroup INT,
    @ToAdd INT,
    @Remainder INT;

DECLARE cursor_Out CURSOR
FOR SELECT OutGroup, OutID, GroupSize, IDInGroup, IDOfGroup, ToAdd, Remainder
    FROM  #OutTable
    WHERE OutGroup > 1 --Leave the first group using the lowest RightName if duplicates
    ORDER BY OutGroup, IDOfGroup;

OPEN cursor_Out;

FETCH NEXT FROM cursor_Out 
INTO @OutGroup, @OutID, @GroupSize, @IDInGroup, @IDOfGroup, @ToAdd, @Remainder;

WHILE @@FETCH_STATUS = 0
    BEGIN
        --NOTE: The cursor starts with the SECOND output group! Leave the first at baseline
        IF @IDOfGroup = 1 BEGIN --
            --For a new block, prepare to count up, but if not a new block keep what it was until used
            SET @TempAdd = 1
        END;
        SET @TempInt = @TempAdd;
        
        IF @GroupSize > 1 BEGIN --If there is only 1 instance of the RightName it can't count up, ignore it
            --Get the last value for this group
            SELECT @LastOutID = OutID, @LastID = IDInGroup
            FROM #OutTable  
            WHERE OutGroup = @OutGroup - 1 AND IDOfGroup = @IDOfGroup

            IF @TempAdd > 0 BEGIN
                --Now we'll increment IDInGroup to cycle through the available copies of RightName
                IF (@LastID + 1) < @GroupSize BEGIN
                    SET @NewID = @LastID + 1;
                    SET @TempAdd = @TempAdd - 1;
                END ELSE BEGIN
                    SET @NewID = 0;
                    --Leave @TempAdd as a carry to also bump up the next
                END; 
            END ELSE BEGIN
                SET @NewID = @LastID 
            END;
            UPDATE #OutTable SET IDInGroup = @NewID WHERE OutID = @OutID;
        END;
        --Debugging
        INSERT INTO #Log (OutIDLog, LogValue) 
        SELECT @OutID, ' @LastID = ' + CONVERT(nvarchar(50), @LastID) + ' to ' + CONVERT(nvarchar(50), @NewID) 
            + ' @TempAdd = ' + CONVERT(nvarchar(50), @TempInt) + ' to ' + CONVERT(nvarchar(50), @TempAdd) 
            + ' @IDOfGroup = ' + CONVERT(nvarchar(50), @IDOfGroup); 

        --Get the next row (if any) and loop
        FETCH NEXT FROM cursor_Out 
        INTO @OutGroup, @OutID, @GroupSize, @IDInGroup, @IDOfGroup, @ToAdd, @Remainder;
    END;

CLOSE cursor_Out;

DEALLOCATE cursor_Out;

--If you want to see the inner workings of the cursor, uncomment the following line
--SELECT OutID, RNBase, GroupSize, IDOfGroup, IDInGroup, OutGroup, LogValue FROM #OutTable as O LEFT OUTER JOIN #Log as L ON O.OutID = L.OutIDLog

--Now put it all together!
SELECT D.AssetCode, D.RightName
    ,  CASE WHEN O.GroupSize = 1 THEN D.RightName
        ELSE CONCAT(D.RightName, '_', CONVERT(nvarchar(50), O.IDInGroup +1)) END as RNExtended
    , ACQUISITIONBEGINDATE, ACQUISITIONENDDATE
    , O.OutGroup
FROM #OutTable as O 
    INNER JOIN #DataWithIDs as D on O.RNBase = D.RIGHTNAME 
        AND O.IDInGroup = D.IDInGroup 

DROP TABLE #Log
DROP TABLE #DataWithIDs
DROP TABLE #OutTable

Which gives the output

AssetCode	RightName	RNExtended	ACQUISITIONBEGINDATE	ACQUISITIONENDDATE	OutGroup
10	R1	R1_1	2023-01-01 00:00:00	2023-10-31 00:00:00	1
10	R2	R2	2023-02-01 00:00:00	2027-12-31 00:00:00	1
10	R3	R3_1	2023-02-01 00:00:00	2024-07-31 00:00:00	1
10	R4	R4	2022-01-01 00:00:00	2026-12-31 00:00:00	1
10	R1	R1_2	2024-04-01 00:00:00	2026-12-31 00:00:00	2
10	R2	R2	2023-02-01 00:00:00	2027-12-31 00:00:00	2
10	R3	R3_1	2023-02-01 00:00:00	2024-07-31 00:00:00	2
10	R4	R4	2022-01-01 00:00:00	2026-12-31 00:00:00	2
10	R1	R1_1	2023-01-01 00:00:00	2023-10-31 00:00:00	3
10	R2	R2	2023-02-01 00:00:00	2027-12-31 00:00:00	3
10	R3	R3_2	2024-10-01 00:00:00	2026-12-31 00:00:00	3
10	R4	R4	2022-01-01 00:00:00	2026-12-31 00:00:00	3
10	R1	R1_2	2024-04-01 00:00:00	2026-12-31 00:00:00	4
10	R2	R2	2023-02-01 00:00:00	2027-12-31 00:00:00	4
10	R3	R3_2	2024-10-01 00:00:00	2026-12-31 00:00:00	4
10	R4	R4	2022-01-01 00:00:00	2026-12-31 00:00:00	4

Answer 2

OK, you asked for an answer without loops (Cursors, I assume), and while the cursor based solution was pretty efficient (a single forward only pass through) and relatively easy to understand, a set based solution is possible so I'll present it here. If you get a chance to compare them I'm curious how different the performance is, but regardless here is your set based answer. Note that it uses no cursors, no temp tables, and no recursive CTEs, so it should be very fast.

EDIT: The original version produced too small a tally table. Normally in pure SQL I'd use ROW_NUMBER on a big system table or view like sys.objects, or maybe specially build one (many articles on the internet about building tally tables) but I'm unfamiliar with Snowflake and wasn't sure if you'd have access to system objects, so I built a bigger one by cross joining cteExp (the expanded names of all RightName instances). I haven't worked out why my original math was wrong or under what conditions this might possibly still be too small (I doubt it though, this will draw from a list bigger than the number of distinct RNs squared) but here is a corrected version. I don't have time to debug this further today, though perhaps this weekend I'll revisit and try to provide commentary.

WITH cteSample as (
    SELECT * FROM (VALUES 
        (CONVERT(nvarchar(20), '10'),CONVERT(nvarchar(50),'R1')
            ,CONVERT(datetime2(0),'2023-01-01 00:00:00.000')
            ,CONVERT(datetime2(0),'2023-10-31 00:00:00.000')),
        ('10','R1','2024-04-01 00:00:00.000','2026-12-31 00:00:00.000'),
        ('10','R1','2022-04-01 00:00:00.000','2025-12-31 00:00:00.000'),--Used for edge testing
        ('10','R1','2021-01-01 00:00:00.000','2025-01-31 00:00:00.000'),--Used for edge testing
        ('10','R1','2020-06-01 00:00:00.000','2024-05-01 00:00:00.000'),--Used for edge testing
        ('10','R1','2020-02-01 00:00:00.000','2021-02-28 00:00:00.000'),--Used for edge testing
        ('10','R1','2023-01-01 00:00:00.000','2024-12-31 00:00:00.000'),--Used for edge testing
        ('10','R2','2023-02-01 00:00:00.000','2027-12-31 00:00:00.000'),
        ('10','R2','2020-01-01 00:00:00.000','2022-12-31 00:00:00.000'),--Used for edge testing
        ('10','R2','2021-01-01 00:00:00.000','2022-12-31 00:00:00.000'),--Used for edge testing
        ('10','R3','2023-02-01 00:00:00.000','2024-07-31 00:00:00.000'),
        ('10','R3','2024-10-01 00:00:00.000','2026-12-31 00:00:00.000'),
        --('10','R3','2024-12-01 00:00:00.000','2025-12-01 00:00:00.000'), --Used for edge testing
        ('10','R4','2022-01-01 00:00:00.000','2026-12-31 00:00:00.000')
        --, ('10','R4','2023-01-01 00:00:00.000','2025-12-31 00:00:00.000')--Used for edge testing
        ) as SampleTable(ASSETCODE, RIGHTNAME, ACQUISITIONBEGINDATE, ACQUISITIONENDDATE)
), cteIDs as (
    SELECT *
        , RIGHTNAME as RNBase
        --We can use IDOverAll if JOIN on RIGHTNAME is a bottleneck, any ID will work
        , ROW_NUMBER() OVER (ORDER BY RIGHTNAME, ACQUISITIONBEGINDATE) -1 as IDOverAll
        --For IDInGroup it MUST be consecutive whole numbers!
        , ROW_NUMBER() OVER (PARTITION BY RIGHTNAME ORDER BY ACQUISITIONBEGINDATE) -1 as IDInGroup
    FROM cteSample as S
), cteBaseRNs as (
    SELECT RNBase, COUNT(*) as GroupSize
        --For IDOfGroup we can use any ID
        , ROW_NUMBER() OVER (ORDER BY RNBase) as IDOfGroup
    FROM cteIDs as S
    GROUP BY RNBase
),cteWeighted as (
    SELECT *
        --exp(sum(log(column))) effectively makes product 
        --see https://stackoverflow.com/questions/3653586/sql-server-query-groupwise-multiplication
        , sum(log(GroupSize)) OVER (ORDER BY IDOfGroup ROWS UNBOUNDED PRECEDING) as PreWeightLog
        , sum(log(GroupSize)) OVER (ORDER BY IDOfGroup DESC ROWS UNBOUNDED PRECEDING) as PostWeightLog
        , log(GroupSize) as ThisWeightLog
    FROM cteBaseRNs
), cteExp as (--Expanded RightNames to include _#
    SELECT I.*, S.GroupSize, S.IDOfGroup
        --If there is only 1 RN acquisition range, leave it bare
        , CASE WHEN S.GroupSize = 1 THEN I.RNBase 
            ELSE CONCAT (I.RNBase, '_', CONVERT(nvarchar(5), IDInGroup))
            END as RNExt --But if more then 1 append _0, _1, ...
    FROM cteBaseRNs as S INNER JOIN cteIDs as I on I.RNBase = S.RNBase 
), cteGroups as (
    SELECT W.RNBase, W.GroupSize, W.IDOfGroup
        , EXP(PreWeightLog - ThisWeightLog) as PreWeight --How many combinations before
        --How many combinations in total
        , EXP(PreWeightLog + PostWeightLog - ThisWeightLog) as TotalWeight 
    FROM cteWeighted as W
),ctePreTally as ( --We need a set of consecutive whole numbers the same size as the output
    --In pure SQL I'd use a view like sys.objects to generate it, but I don't know what's 
    --available in snowflake, so I'll overbuild from CROSS JOINS on tables we have
    SELECT ROW_NUMBER() OVER (ORDER BY E.IDOverAll) - 1 as Tally 
        , G.TotalWeight as NumberOfCombos
        , (SELECT COUNT(*) FROM cteGroups) as NumberOfGroups
    FROM cteExp as E CROSS JOIN cteExp as E2 CROSS JOIN cteGroups as G
),cteTally as ( --We need a set of consecutive whole numbers the same size as the output
    SELECT Tally, NumberOfCombos, NumberOfGroups
    FROM ctePreTally
    WHERE Tally < NumberOfCombos * NumberOfGroups
), ctePreOut as ( --Build the output list with each distinct RN for each of the number of combinations
    SELECT G.*, T.Tally + 1 as OutGroup --For user display
        , CONVERT(bigint,FLOOR(T.Tally/PreWeight)) % GroupSize as IDInGroup
    FROM cteTally as T CROSS JOIN cteGroups as G
    WHERE T.Tally < NumberOfCombos 
) SELECT --O.*, '---', G.* --Now build the final output and put it in the right order, 
    --adding back in the details we didn't carry like Acqutsition dates
    G.AssetCode, G.RightName, G.RNExt, G.ACQUISITIONBEGINDATE, G.ACQUISITIONENDDATE, O.OutGroup
FROM ctePreOut as O 
    INNER JOIN cteExp as G 
        on O.RNBase = G.RNBase AND O.IDInGroup = G.IDInGroup 
ORDER BY O.OutGroup, O.IDOfGroup

This produces the following output

AssetCode	RightName	RNExt	ACQUISITIONBEGINDATE	ACQUISITIONENDDATE	OutGroup
10	R1	R1_0	2023-01-01 00:00:00	2023-10-31 00:00:00	1
10	R2	R2	2023-02-01 00:00:00	2027-12-31 00:00:00	1
10	R3	R3_0	2023-02-01 00:00:00	2024-07-31 00:00:00	1
10	R4	R4	2022-01-01 00:00:00	2026-12-31 00:00:00	1
10	R1	R1_1	2024-04-01 00:00:00	2026-12-31 00:00:00	2
10	R2	R2	2023-02-01 00:00:00	2027-12-31 00:00:00	2
10	R3	R3_0	2023-02-01 00:00:00	2024-07-31 00:00:00	2
10	R4	R4	2022-01-01 00:00:00	2026-12-31 00:00:00	2
10	R1	R1_0	2023-01-01 00:00:00	2023-10-31 00:00:00	3
10	R2	R2	2023-02-01 00:00:00	2027-12-31 00:00:00	3
10	R3	R3_1	2024-10-01 00:00:00	2026-12-31 00:00:00	3
10	R4	R4	2022-01-01 00:00:00	2026-12-31 00:00:00	3
10	R1	R1_1	2024-04-01 00:00:00	2026-12-31 00:00:00	4
10	R2	R2	2023-02-01 00:00:00	2027-12-31 00:00:00	4
10	R3	R3_1	2024-10-01 00:00:00	2026-12-31 00:00:00	4
10	R4	R4	2022-01-01 00:00:00	2026-12-31 00:00:00	4