0

I'm grouping the months according to two date ranges. The months will change in each posted date range. I need to convert months into columns. Dates can also cover the next year. I need to do this in HQL.

I have a table like this:


 | ID   |   Product|  Month   | Sales  
 | 1    |    A     |   8      |   500
 | 2    |    B     |   8      |   200
 | 2    |    B     |   9      |   600
 | 3    |    C     |   9      |   700
 | 4    |    D     |   9      |   
 | 4    |    D     |   10     |
 | 4    |    D     |   11     |
 | 5    |    E     |   11     |

I want it to be like this:


 | ID   |   Product|  8   |  9 | 10 | 11 |  
 | 1    |    A     |  500 |    |    |    |
 | 2    |    B     |  200 |600 |    |    |
 | 3    |    C     |      |700 |    |    |
 | 4    |    D     |      |  # | #  | #  |
 | 5    |    E     |      |    |    | #  |

Thanks

michael schmid
  • 1
  • 1
  • This is a task much more suited for your presentation manager. While SQL can with `crosstab` accomplish the pivot operation (as @TomášZáluský's suggests) it must still *know the exactly columns in the result* set. This means that next month you must change the query,or build it dynamically - i.e. write code to write the query. Much better (imho) to perform the formatting in the app's presentation layer. – Belayer Nov 22 '22 at 17:20

1 Answers1

1

What you need is called pivot operation. The design of HQL emphasizes the object graph nature of data. It is not as feature-rich as native SQL. It suits quite good to tasks where associations between objects are traversed. Pivot is often used as analytical operation. I am not active HQL user but I guess it is too "high level" operation to be directly implemented in HQL. It can be emulated by group by + case + when, which could be possibly rewritten into HQL. You can alternatively implement it in native SQL, using either group by + case + when emulation or crosstab function which must be installed as an extension.

Tomáš Záluský
  • 10,735
  • 2
  • 36
  • 64