I was wondering if there was a way to write HQL to solve the following problem which was already posted has a solution in native SQL. In fact the alternative solution using HQL takes humongous time as I have re-loop so many times to create a solution. Is writing SQL queries the only solution?

Source: http://www.experts-exchange.com/Databas ... 87594.html

I have a table Company with columns like Phone_Num, Fax and email where they can have multiple values.
These values should be return in a single row.
The table below shed more light on the scenario
Company | Phone_Num | Fax | email
AG 0345 45 ab@rt.com
AG 0378 47 ab@rt.com
GB 0256 34 gb@rt.com
GB 0298 34 gb2@rt.com
RD 0449 67 rd@rt.com

I would the result of my query be

Company | Phone_Num | Fax | email
AG 0345,0378 45,47 ab@rt.com
GB 0256,0256 34 gb@rt.com,gb2@rt.com
RD 0449 67 rd@rt.com

I am thinking of creating a function given the name of the company, it returns a
string in the pattern the "Phone_NUm#Fax&email". Using string functions like substring and length
I would split it in the respective variables.
My problem is how can I create the oracle function.
Any help would be welcome.

Solution:
sdstuber:
You are aggregating your data, so you must use a GROUP BY or an analytic...
If you're concerned with the complexity of STRAGG, don't be.
A user defined aggregate is what you're trying to write, so you would just be reinventing that function
anyway.

Plus, Tom Kyte's site provides all the code and is ready to run "as is" so there's really no downside to it.


To use stragg as an analytic instead of an aggregate, it would look something like this...

SELECT DISTINCT company,
stragg(DISTINCT phone_num) over (partition by company) phone_num,
stragg(DISTINCT fax) over (partition by company) fax,
stragg(DISTINCT email) over (partition by company) email
FROM your_table