hive-user mailing list archives

Hello,
How do I get count from a list of comma separated values? For the lack of better wording,
here is an example:
Suppose there is a table with two columns, id (integers) and values (string) in the following
fashion.
id values
1 10,20,30
2 20,30
2 20
3 20,30,40
3 40,20,30
I want an output like this:
id value count
1 10 1
1 20 1
1 30 1
2 20 2
2 30 1
3 20 2
3 30 2
3 40 2
So, it's a list containing the values those appear against each id, along with their count.
I understand this is more of a SQL kind of question rather than one specific to Hive, but
I'm at a roadblock here.
Thanks,
Saurabh