Databases
StackOverflow2013
Postsdbo
POSQL Server aggregate rows into column values
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server aggregate rows into column values
    text
    copied!<p>first time post on this site but I've used it a lot in the past. I did try to search for an answer to this but couldn't nail down how to frame the question properly, so here goes.</p> <p>I have a table where each row has an evaluation number of 1, 2, 3 or 4. Each row also has an ID that is a foreign key to a membership table, and another ID that is a foreign key to other data related to the row (a table that holds test information). </p> <p>What I need to do is filter this list of records by the membership group, and then create an output query of some type that lists:</p> <ol> <li>The test ID</li> <li>The number of records that are evaluated to 1</li> <li>The number of records that are evaluated to 2</li> <li>The number of records that are evaluated to 3</li> <li>The number of records that are evaluated to 4</li> </ol> <p>I can work out how to get the count of each evaluation number separately using something like:</p> <pre><code>SELECT Count(Eval), scores.TestID FROM membership INNER JOIN (scores INNER JOIN tests ON scores.TestID = tests.TestID) ON membership.ID = scores.ClientID WHERE membership.GroupID = &lt;MembershipGroup&gt; AND scores.Eval = 1; </code></pre> <p>Where in the above is the main filter, scores.TestID is the 'grouping' ID, and Count(Eval) is the aggregate I want. Essentially my ideal output is:</p> <pre> ID |Count1|Count2|Count3|Count4 ----------------------------------- 100 |5 |8 |9 |12 101 |16 |2 |14 |11 </pre> <p>...and such. Again, sorry if this was a garbled mess, hopefully you lot can help!</p> <p>PS. The resultant query will be retrieved from ASP.NET to bind to a ListView - if anyone can advise on whether I am better off writing this up as a stored procedure and looking that up or coding it into the SQLDataSource in ASP that would be much appreciated :-)</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