0

select * from mytable gives me a result set like:

evaluation | item | criteria | points
         1 | 1    | 1        | 111
         1 | 1    | 2        | 112
         1 | 2    | 1        | 121
         1 | 2    | 2        | 122
         2 | 1    | 3        | 213
         2 | 2    | 3        | 223
         2 | 3    | 3        | 233

and I want to end up with a view that puts all criteria used in an evaluation in the same row, for example:

select * from myview where evaluation = 1 would get me

evaluation | item | points_1 | points_2
         1 | 1    | 111      | 112
         1 | 2    | 111      | 112

and the result set for select * from myview where evaluation = 2 would be:

evaluation | item | points_3
         2 | 1    | 213    
         2 | 2    | 223     
         2 | 3    | 233

is that possible at all?

NotGaeL
  • 8,344
  • 5
  • 40
  • 70
  • 5
    This is not possible. A view has a fixed set of columns when it is defined. – Gordon Linoff Jul 27 '15 at 11:44
  • More specifically, a view is defined by a single select query. *Any* specific query always has a fixed "shape" - the number of columns within it, the *types* of those columns and their names. The only way to get variable result sets is to construct new queries at runtime - but that needs more than a single select query. – Damien_The_Unbeliever Jul 27 '15 at 11:48
  • ok, that's what I thought. I guess I'll have to fix the result set with some coding then ^^ – NotGaeL Jul 27 '15 at 11:51
  • You could do a stored procedure that returns variable number of columns using dynamic SQL – James Z Jul 27 '15 at 11:51
  • it will be application code. Stored procedures are not permitted on this project (Although I don't care. It's pretty much the same effort...) – NotGaeL Jul 27 '15 at 11:53
  • Why can't you just have extra columns that have `NULL` values? – Gordon Linoff Jul 27 '15 at 17:10
  • that would be a useful alternative, but since I have no way of knowing how many criteria will be added to the table, the view would still have a variable number of rows, so it is not possible either, right? – NotGaeL Jul 27 '15 at 18:45

0 Answers0