I have a table with 5 columns in HANA.The table name is students and following are the fields- name, age, address,gender,score.I need to get the distinct values of each of the columns.For eg, the table is-
Name Age Address Gender Score
A 1 abc F 10
C 3 abc M 10
B 2 def M 5
C 3 ghi F 10
D 2 def M 5
Expected results is-
Name Age Address Gender Score
A 1 abc F 10
B 2 def M 5
C 3 ghi
Please suggest how to achieve this using views or odata.None of the discussions available seem to be useful.I do not want UNION because it combines all the values into one column in the result.
I don't have access to Hana, but this might do the job
select min (name) as name
,min (Age) as Age
,min (Address) as Address
,min (Gender) as Gender
,min (Score) as Score
from ( select Name ,Age ,Address ,Gender ,Score ,1000000000 as n from students where 1=2
union all select Name ,null ,null ,null ,null ,row_number() over (order by Name) from students group by Name
union all select null ,Age ,null ,null ,null ,row_number() over (order by Age) from students group by Age
union all select null ,null ,Address ,null ,null ,row_number() over (order by Address) from students group by Address
union all select null ,null ,null ,Gender ,null ,row_number() over (order by Gender) from students group by Gender
union all select null ,null ,null ,null ,Score ,row_number() over (order by Score) from students group by Score
) s
group by n
order by n
;
+------+-----+---------+--------+--------+
| name | age | address | gender | score |
+------+-----+---------+--------+--------+
| A | 1 | abc | F | 5 |
+------+-----+---------+--------+--------+
| B | 2 | def | M | 10 |
+------+-----+---------+--------+--------+
| C | 3 | ghi | | |
+------+-----+---------+--------+--------+
For educational purposes, here are the results of the inner query:
+------+-----+---------+--------+-------+---+
| name | age | address | gender | score | n |
+------+-----+---------+--------+-------+---+
| A | | | | | 1 |
+------+-----+---------+--------+-------+---+
| B | | | | | 2 |
+------+-----+---------+--------+-------+---+
| C | | | | | 3 |
+------+-----+---------+--------+-------+---+
| | 1 | | | | 1 |
+------+-----+---------+--------+-------+---+
| | 2 | | | | 2 |
+------+-----+---------+--------+-------+---+
| | 3 | | | | 3 |
+------+-----+---------+--------+-------+---+
| | | abc | | | 1 |
+------+-----+---------+--------+-------+---+
| | | def | | | 2 |
+------+-----+---------+--------+-------+---+
| | | ghi | | | 3 |
+------+-----+---------+--------+-------+---+
| | | | F | | 1 |
+------+-----+---------+--------+-------+---+
| | | | M | | 2 |
+------+-----+---------+--------+-------+---+
| | | | | 5 | 1 |
+------+-----+---------+--------+-------+---+
| | | | | 10 | 2 |
+------+-----+---------+--------+-------+---+