Pivot Table and ORDER BY -problem

[Andrzej]

Hello

I use MS SQL 2000. I need to present some rows as new columns.
Thanks to help hspc (http://www.codeguru.com/forum/member.php?u=73157) I know how to do it but ...

I think that my query ( please find enclosed file ) is simply wrong.
To get result without error I need to add one extra column R.ReelGrade. Without this column I returned an error:
Column R.ReelGrade is invalid in the selected list because it is not contained in either an aggregatefunction or the GROUP BY clasue

I don't need to ORDER BY the second column. My oryginal query will SELECT at least 20 columns, I don't want to add 20 extra column to ORDER BY calsue.
Could you please tell me what my query should be.

PS
I found some info about it hereSQL GROUP BY techniques (http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx) but it is not clear enough for me.

Thanks in advance

[dglienna]

If you download the SQL Server Mgmt Studio, and SQLExpress, you can use the wizard to design your query, and run it.

[Alsvha]

Basically, when you use a Group By, then you can't select any information from the query which isn't in the Group By clause or in an aggregated function.

The reason for this is that when you run "Group By" you combine multiple rows into one row. And if the column then isn't "Grouped" or aggregated, it means multiple values of the same column would be in the row, and that can't be selected.

I'm not sure what is wrong with your query though, as I've not looked too much, but if you want to select ReelGrade out without having it aggregated or in the group by clause, the only method you have is to either make it a sub query, or select the result into a temporary table, and use that as foundation to join into the information about ReelGrade.

Hope this helps.

[dglienna]

Are your tables called this?

ReelGradeData
ReelGradeDetail

as you have in your labels?

[Andrzej]

Are your tables called this?

ReelGradeData
ReelGradeDetail

as you have in your labels?


yes, my tables name are:

Table1 Name / "ReelGradeData"
Table2 Name / "ReelGradeDetail"