Work often meet a lot of demand is this, according to the conditions to summarize some fields, such as I encountered is, our company has three investment platform, the same customer with a mobile phone number in three platforms are registered, but the registered user name is not the same, when the display needs to display all registered names according to the phone number. (I'm using an Oracle database)
1, the original data is this,
2, the requirements shown in this way,
I checked the Oracle function, this listagg function can satisfy it, looked at on the net, all is introduces Listagg This function the use method, looks really laborious, therefore here I concisely explain briefly
3, the concrete realization is this, also does not have nonsense, after reading the example all can understand:
Select Phone, Listagg (Log_name, ', ') within group (order by phone) logName
From Int_phone
where phone = ' 13350162230 '
GROUP BY phone
4. Matters needing attention are as follows:
(1). Must score group, i.e. group by is required.
(2). The first parameter of the Listagg function is the field that needs to be displayed, that is, log_name; the second argument is a delimiter between the numbers, and it also needs to be sorted and grouped within group (order by name)
[Reprint]oracle function Listagg instructions for use