I have a sql view, let's call it SampleView, whose results have the following format.
Id (INT), NameA (VARVHAR(50)), NameB (VARCHAR(50)), ValueA (INT), ValueB (INT)
The result set of the view contains rows that may have the same Id or not. When there are two or more rows with the same Id, I would like to get something like the following
SELECT
Id,
MAX(NameA),
MAX(NameB),
MAX(ValueA),
MAX(ValueB)
FROM SampleView
GROUP BY Id
ORDER BY Id
Regarding the columns Id, ValueA and ValueB there isn't any problem. On the other hand using MAX for both NameA and NameB things are not as expected. After some googling and searching I realized that MAX has not the "expected" behavior for alphanumeric columns. Saying the expected, I mean using MAX in my case, it would be to return the value of NameA with the maximum number of characters, MAX(LEN(NameA)). I have to mention here that there ins't any possibility for NameA to have two values for the same Id with the same length. This might makes the problem more easy to be solved.
I use SQL Server 2012 and TSQL.
Have you any suggestion on how I could deal with this problem?
Thank you very much in advance for any help.