I have a table with a many to many relationship, in which I need to make a 1 to 1 without modifying the schema. Here is the pseudo code:

 Reports  { 
      Id INT,  
      Description NVARCHAR(256),
      ReportFields...  
 }
 ScheduledReports { 
    ScheduledReportId INT
    ReportId INT (FK)
    Frequency INT
 }

When I run this query:

SELECT [ReportID], COUNT(*) as NumberOfReports
FROM [ScheduledReports]
GROUP BY ReportId
HAVING COUNT(*) > 1

I get return the results of all the reports who have duplicates.

ReportId,  NumberOfReports
   1,         2
   2,         4

Foreach additional report (e.g NumberOfReports -1).
I need to create a duplicate row in the Reports table. However I'm having trouble on figuring out how to turn the count into a join (since I don't want to use cursors).

Here is my query:

INSERT INTO Reports (Description)
SELECT Description 
FROM Reports 
WHERE ReportId IN  (SELECT [ReportID]
                    FROM [ScheduledReports]
                    GROUP BY ReportId
                    HAVING COUNT(*) > 1)

How do I Join the ReportRow on itself for Count(*) -1 times?

CodePudding user response：

The below query should get you a sequencing of the schedules per unique report. You can then use the sequencing > 1 to determine which values will need to be inserted to your report table. Output of this select should probably be cached, since it will

1. Indicate which rows need to be added to your Reports by their current ID
2. Can be used to later update the referenced ReportID in your schedules table

SELECT * 
FROM (
    SELECT Reports.Id
        ,ScheduledReportId
        ,ROW_NUMBER() OVER (
            PARTITION BY ReportId
            ORDER BY ScheduledReportId
        ) AS [Sequencing]
    FROM Reports 
        INNER JOIN ScheduledReports on ScheduledReports.ReportId = Reports.Id
    WHERE ReportId IN  (SELECT [ReportID]
                        FROM [ScheduledReports]
                        GROUP BY ReportId
                        HAVING COUNT(*) > 1)) AS SequencedReportAndSchedules