Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here is my solution <a href="http://sqlfiddle.com/#!3/b5b38/1/0" rel="nofollow">SQL Fiddle</a></p> <p>The nature of the relationships require looping as I figure.</p> <p><BR> Here is the SQL:</p> <pre><code>--drop TABLE Table1 CREATE TABLE Table1 ([row] int identity (1,1),GroupID INT NULL,[Company] varchar(2), [Publisher] varchar(2)) ; INSERT INTO Table1 (Company, Publisher) select left(newid(), 2), left(newid(), 2) declare @i int = 1 while @i &lt; 8 begin ;with cte(Company, Publisher) as ( select left(newid(), 2), left(newid(), 2) from Table1 ) insert into Table1(Company, Publisher) select distinct c.Company, c.Publisher from cte as c where not exists (select * from Table1 as t where t.Company = c.Company and t.Publisher = c.Publisher) set @i = @i + 1 end; CREATE NONCLUSTERED INDEX IX_Temp1 on Table1 (Company) CREATE NONCLUSTERED INDEX IX_Temp2 on Table1 (Publisher) declare @counter int=0 declare @row int=0 declare @lastnullcount int=0 declare @currentnullcount int=0 WHILE EXISTS ( SELECT * FROM Table1 where GroupID is null ) BEGIN SET @counter=@counter+1 SET @lastnullcount =0 SELECT TOP 1 @row=[row] FROM Table1 where GroupID is null order by [row] asc SELECT @currentnullcount=count(*) from table1 where groupid is null WHILE @lastnullcount &lt;&gt; @currentnullcount BEGIN SELECT @lastnullcount=count(*) from table1 where groupid is null UPDATE Table1 SET GroupID=@counter WHERE [row]=@row UPDATE t2 SET t2.GroupID=@counter FROM Table1 t1 INNER JOIN Table1 t2 on t1.Company=t2.Company WHERE t1.GroupID=@counter AND t2.GroupID IS NULL UPDATE t2 SET t2.GroupID=@counter FROM Table1 t1 INNER JOIN Table1 t2 on t1.publisher=t2.publisher WHERE t1.GroupID=@counter AND t2.GroupID IS NULL SELECT @currentnullcount=count(*) from table1 where groupid is null END END SELECT * FROM Table1 </code></pre> <p>Edit: Added indexes as I would expect on the real table and be more in line with the other data sets Roman is using.</p>
    singulars
    1. This table or related slice is empty.
    1. POGroup All Related Records in Many to Many Relationship, SQL graph connected components
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USUnhandledExcepSean
    1. USUnhandledExcepSean
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO@GoatCO Unless the question is closed, new answers may appear. Up- and down-votes will change state too. And then your comment may loose its truth value. When revering to other answers, you can use links to the 'share'-urls and refrain from using terms as 'the other', 'above' and 'below'. In sql terms it's like recording 'birthday' instead of 'age'; you're referring to some immutable instead of a variable.
      singulars
      1. PO
      1. USChris Wesseling
    2. CO@GoatCO FYI, after performance testing, my solution was markedly better performing on my local SQL server instance than any of the CTE examples I've seen in this thread. Asking Roman to add it to his performance.
      singulars
      1. PO
      1. USUnhandledExcepSean
    3. COI've run all answers through a battery of examples, from 1000 records to 1 million records, and found otherwise, but I will re-visit it.
      singulars
      1. PO
      1. USHart CO
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload