Let's first notice that the maximal amount of water contained in a histogram with $N$ columns is exactly $(N-1)\times (N-2)/2$. The configuration to achieve this is, for example, $[N,1,2,\dots ,N-1]$.

The main idea of the solution is to remove several columns from the middle and put them in descending order on the side. Then the total amount of water would depend only on the columns left in the middle. A column of height $h$ in the middle would contribute $N-1-h$ water.

Let's assume that we left columns of height $h_1,h_2,\dots ,h_k$ in the middle. If we use the notation $v_i=N-1-h_i$, then we have reduced the problem to finding a subset of numbers $\{1,2,\dots ,N-2\}$ which sum is precisely $X$. Such numbers can be found using a greedy algorithm. The proof is left as an exercise to the reader.