A table with data like this: Id | Type | val1 | val2 ... 12 | B3 | 21.9 | 32.5 12 | C2 | 12.4 | 21.3 ... 13 | B3 | 19.2 | 11.4 13 | C2 | 32.5 |61.2
Now I need show the data this way: | Type | val1 | val1 | val2 | val2 ... | B3 | 21.9 | 19.2 | 32.5 | 11.4 | C2 | 12.4 | 32.5 | 21.3 | 61.2 The tricky part is the weekId 13 data must be arranged on the same line with weekId 12. Possible? Thanks in advance.
MAX(CASE WHEN Id = 12 THEN val1 END) AS val1,
MAX(CASE WHEN id = 13 THEN val1 END) AS val1,
MAX(CASE WHEN Id = 12 THEN val2 END) AS val2,
MAX(CASE WHEN id = 13 THEN val2 END) AS val2
Tried something and actually MAX() here does not need get maximum or minimum values but just make the case when statement work. So if you change it to MIN() you get same results. Is it right? Thanks.
Yes - the MAX or MIN (or any aggregate function that is applicable to the data type) can work. The assumption is that there will be only one value that satisfies the grouping condition for a given Id and Type, so you are not losing any data, but it allows you to group by the Type column.