StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POError on grouping on a single element in the select list
text
Body
copied!<p>I have a query need (question really) that I would like to group the results by a value from a table with a where clause from another table. The scenario looks like this:</p> <p>Table1 (Single row for each description, no dupes)</p> <ul> <li>int id</li> <li>varchar(10) description (Single row for each description, no dupes)</li> </ul> <p>Table2 multiple rows for each Table1.ID)</p> <ul> <li>int id</li> <li>fk_ref_tbl_1_ID (id from Table1)</li> <li>varchar(30) SomeText</li> </ul> <p>Table3 (multiple rows for each Table2.ID)</p> <ul> <li>int id</li> <li>fk_ref_tbl_2_ID (id from Table2)</li> <li>varchar(30) ChoicesForTable2</li> </ul> <p>The output I am desiring is something like this:</p> <p>Table2.SomeText</p> <ul> <li>Table3.ChoicesForTable2</li> <li>Table3.ChoicesForTable2</li> <li>Table3.ChoicesForTable2</li> </ul> <p>my query so far looks like this:</p> <pre><code>select Table2.Some, Table3.ChoicesForTable2 from Table1 t1 inner join Table2 t2 on T1.id = T2.fk_ref_tbl_1_ID inner join Table3 t3 on T3.fk_ref_tbl_2_ID = T2.ID where T1.id = 45 group by T2.id </code></pre> <p>I get an an error:</p> <blockquote> <p>Msg 8120, Level 16, State 1, Line 1 Column 'T2.SomeText' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p> </blockquote> <p>without the group by I am getting something like this:</p> <p>T2.SomeText, T3.ChoicesForTable2 (each T3.ChoicesForTable2 has a different value) T2.SomeText, T3.ChoicesForTable2 (each T3.ChoicesForTable2 has a different value) T2.SomeText, T3.ChoicesForTable2 (each T3.ChoicesForTable2 has a different value) T2.SomeText, T3.ChoicesForTable2 (each T3.ChoicesForTable2 has a different value) T2.SomeText, T3.ChoicesForTable2 (each T3.ChoicesForTable2 has a different value)</p> <p>Think of Table1 as a Test, each test has many questions (Table2), and each question has several possible answers (Table3). So ideally I would be able to generate a output where specific question is displayed and the possible choices right below it. So my grouping would by the question (Table2)</p> <p>I am really trying to do this all in T-SQL without cursors, I can brute force it with C# and then generate the test using SSRS, the more efficient way i think is to just come up with the right query to bind to the ssrs definition. Since the real stumbling block for me here is the select with multiple fields and grouping, solving this will certainly ease the pain going forward.</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