Databases
StackOverflow2013
Postsdbo
POCross join (pivot) with n-n table containing values
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCross join (pivot) with n-n table containing values
    text
    copied!<p>I have 3 tables :</p> <pre><code>TABLE MyColumn ( ColumnId INT NOT NULL, Label VARCHAR(80) NOT NULL, PRIMARY KEY (ColumnId) ) TABLE MyPeriod ( PeriodId CHAR(6) NOT NULL, -- format yyyyMM Label VARCHAR(80) NOT NULL, PRIMARY KEY (PeriodId) ) TABLE MyValue ( ColumnId INT NOT NULL, PeriodId CHAR(6) NOT NULL, Amount DECIMAL(8, 4) NOT NULL, PRIMARY KEY (ColumnId, PeriodId), FOREIGN KEY (ColumnId) REFERENCES MyColumn(ColumnId), FOREIGN KEY (PeriodId) REFERENCES MyPeriod(PeriodId) ) </code></pre> <p>MyValue's rows are only created when a real value is provided.</p> <p>I want my results in a tabular way, as :</p> <pre><code>Column | Month 1 | Month 2 | Month 4 | Month 5 | Potatoes | 25.00 | 5.00 | 1.60 | NULL | Apples | 2.00 | 1.50 | NULL | NULL | </code></pre> <p>I have successfully created a cross-join :</p> <pre><code>SELECT MyColumn.Label AS [Column], MyPeriod.Label AS [Period], ISNULL(MyValue.Amount, 0) AS [Value] FROM MyColumn CROSS JOIN MyPeriod LEFT OUTER JOIN MyValue ON (MyValue.ColumnId = MyColumn.ColumnId AND MyValue.PeriodId = MyPeriod.PeriodId) </code></pre> <p>Or, in linq :</p> <pre><code>from p in MyPeriods from c in MyColumns join v in MyValues on new { c.ColumnId, p.PeriodId } equals new { v.ColumnId, v.PeriodId } into values from nv in values.DefaultIfEmpty() select new { Column = c.Label, Period = p.Label, Value = nv.Amount } </code></pre> <p>And seen how to create a pivot in linq (<a href="https://stackoverflow.com/questions/183585/sql-command-to-linq-pivoting">here</a> or <a href="https://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq">here</a>) :</p> <p>(assuming MyDatas is a view with the result of the previous query) :</p> <pre><code>from c in MyDatas group c by c.Column into line select new { Column = line.Key, Month1 = line.Where(l =&gt; l.Period == "Month 1").Sum(l =&gt; l.Value), Month2 = line.Where(l =&gt; l.Period == "Month 2").Sum(l =&gt; l.Value), Month3 = line.Where(l =&gt; l.Period == "Month 3").Sum(l =&gt; l.Value), Month4 = line.Where(l =&gt; l.Period == "Month 4").Sum(l =&gt; l.Value) } </code></pre> <p>But I want to find a way to create a resultset with, if possible, Month1, ... properties dynamic.</p> <p>Note : A solution which results in a n+1 query :</p> <pre><code>from c in MyDatas group c by c.Column into line select new { Column = line.Key, Months = from l in line group l by l.Period into period select new { Period = period.Key, Amount = period.Sum(l =&gt; l.Value) } } </code></pre>
 

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