StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I've decided to post this, just for the sake of a procedural solution appearing in parallel with the CTE-based ones. </p> <p>The following produces a <em><strong>zero-based</strong></em> <code>GroupKey</code> column. If you want it to start from 1, simply change <code>@i</code> to <code>@i+1</code> in the last <code>insert ... select</code>.</p> <pre><code>-- Add a zero-based row number, partitioned by AttributeId declare @Attrs table (AttributeId int,Value varchar(50),RowNum int) insert into @Attrs select AttributeId,Value, ROW_NUMBER()over(partition by AttributeId order by AttributeId,Value)-1 from _Combinations -- AttributeId value counts declare @AttCount table (AttributeId int,n int) insert into @AttCount select AttributeId,COUNT(*) n from @Attrs group by AttributeID -- Total number of combos -- Multiply all AttributeId counts -- EXP(SUM(LOG(n))) didnt work as expected -- so fall back to good old cursors... declare @ncombos int,@num int declare mulc cursor for select n from @AttCount open mulc set @ncombos=1 fetch next from mulc into @num while @@FETCH_STATUS=0 begin set @ncombos=@ncombos*@num fetch next from mulc into @num end close mulc deallocate mulc -- Now let's get our hands dirty... declare @i int,@m int,@atid int,@n int,@r int declare c cursor for select AttributeId,n from @AttCount open c fetch next from c into @atid,@n set @m=1 while @@FETCH_STATUS=0 begin set @i=0 while @i<@ncombos begin set @r=(@i/@m)%@n insert into _CombinedAttributes (GroupKey,AttributeId,Value) select @i,@atid,value from @Attrs where AttributeId=@atid and RowNum=@r set @i=@i+1 end set @m=@m*@n fetch next from c into @atid,@n end close c deallocate c </code></pre> <p><strong>Hint</strong>: <a href="http://sqlfiddle.com/#!6/ffa58/12" rel="nofollow">Here's why I didn't use <code>exp(sum(log()))</code> to emulate a <code>mul()</code> aggregate</a>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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