Sample Data
| A | B | C | M | V |
|---|---|---|---|---|
| a11 | b11 | c11 | m1 | 10 |
| a11 | b11 | c11 | m2 | 20 |
| a11 | b11 | c11 | m3 | 30 |
| a12 | b11 | c11 | m1 | 100 |
| a12 | b11 | c11 | m3 | 300 |
| a13 | b11 | c11 | m1 | 1000 |
| a13 | b11 | c11 | m2 | 2000 |
| a13 | b11 | c11 | m3 | 3000 |
Expected OUTPUT
| A | B | P1 | P2 | P3 |
|---|---|---|---|---|
| a11 | b11 | (c11,m1,10) | (c11,m2,20) | (c11,m3,30) |
| a12 | b11 | (c11,m1,100) | ('','',0) | (c11,m3, 300) |
| a13 | b11 | c11.m1,1000) | (c11,m2,2000) | (c11,m3,3000) |
The above output can then be easily converted using java to below UI table format. The UI will display data as below and one can sort the c11,m1 or c11,m2 or c11,m3 column in asc or desc order. This is like pivoting few set of columns to display data.
| A | B | c11,m1 | c11.m2 | c11,m3 |
|---|---|---|---|---|
| a11 | b11 | 10 | 20 | 30 |
| a12 | b11 | 100 | NULL | 300 |
| a13 | b11 | 1000 | 2000 | 3000 |
OUTPUT Generated By Query
WITH
groupArray(tuple(C,M,V)) as grouped_array,
arrayElement(grouped_array,1) as p1,
arrayElement(grouped_array,2) as p2,
arrayElement(grouped_array,3) as p3
SELECT A, B,p1,p2,p3
FROM sample_data
GROUP BY A,B
order by p1.3 desc
OUPUT Generated by above Query shows that the missing value for c11,m2 should be part of p2 and not p3. This will not work when sorting the data with p2.
| A | B | P1 | P2 | P3 |
|---|---|---|---|---|
| a11 | b11 | (c11,m1,10) | (c11,m2,20) | (c11,m3,30) |
| a12 | b11 | (c11,m1,100) | (c11,m3,300) | ('','',0) |
| a13 | b11 | c11.m1,1000) | (c11,m2,2000) | (c11,m3,3000) |