on 05-06-2015 8:17 AM
Hi everyone!
We have two simple tables:
CREATE COLUMN TABLE szharko.city_test (city_id VARCHAR(20) PRIMARY KEY, city_name VARCHAR(20));
insert into "SZHARKO"."CITY_TEST" values('1', 'Berlin');
insert into "SZHARKO"."CITY_TEST" values('2', 'Munich');
insert into "SZHARKO"."CITY_TEST" values('3', 'Minsk');
insert into "SZHARKO"."CITY_TEST" values('4', 'Gomel');
CREATE COLUMN TABLE szharko.enterprise_test (city_id VARCHAR(20), enterprise_name VARCHAR(20) PRIMARY KEY);
insert into "SZHARKO"."ENTERPRISE_TEST" values('1', 'Enterprise A');
insert into "SZHARKO"."ENTERPRISE_TEST" values('1', 'Enterprise B');
insert into "SZHARKO"."ENTERPRISE_TEST" values('2', 'Enterprise C');
insert into "SZHARKO"."ENTERPRISE_TEST" values('2', 'Enterprise D');
insert into "SZHARKO"."ENTERPRISE_TEST" values('3', 'Enterprise E');
insert into "SZHARKO"."ENTERPRISE_TEST" values('3', 'Enterprise F');
insert into "SZHARKO"."ENTERPRISE_TEST" values('4', 'Enterprise G');
insert into "SZHARKO"."ENTERPRISE_TEST" values('4', 'Enterprise H');
How can we make this table of the above two ones:
CITY_NAME | ENTERPRISES |
---|---|
Munich | Enterprise D, Enterprise C |
Minsk | Enterprise F, Enterprise E |
Gomel | Enterprise H, Enterprise G |
Berlin | Enterprise B, Enterprise A |
I tried to make the following request:
select city_name, enterprises
from
(
select city.city_name, enterprise.enterprises
from
( select city_id, city_name from szharko.city_test ) city
left join
(
select city_id, string_agg(enterprise_name, ', ') as enterprises from
( select city_id, enterprise_name from szharko.enterprise_test order by enterprise_name desc )
group by city_id
) enterprise on city.city_id = enterprise.city_id
order by city_name desc
);
But it returns another table (enterprises sorted within rows in the wrong order, ASC instead DESC):
CITY_NAME | ENTERPRISES |
---|---|
Munich | Enterprise C, Enterprise D |
Minsk | Enterprise E, Enterprise F |
Gomel | Enterprise G, Enterprise H |
Berlin | Enterprise A, Enterprise B |
Can I solve the original problem?
Thank you in advance!
I tried and it works for me like this
SELECT CITY_ENT.CITY_NAME, STRING_AGG( CITY_ENT.ENTERPRISE_NAME,',')
FROM (
SELECT CITY_NAME, ENTERPRISE_NAME FROM "DK"."CITY_TEST"
LEFT JOIN "DK"."ENTERPRISE_TEST" ON "CITY_TEST".CITY_ID = "ENTERPRISE_TEST".CITY_ID
ORDER BY CITY_NAME, ENTERPRISE_NAME DESC ) AS CITY_ENT
GROUP BY CITY_ENT.CITY_NAME ;
MAybe I got wrong something.
Anyways I have to say Thank You for asking question in a perfect way...with DDL with table inserts, with Your tried select and result You expect. Example of Hw to do it, because with my daily Job theres like 5 min window to try to answer interesting question..
Anyway Thx.
EDIT: Even better would be if I said which version of Hana I use ( 1.00.093.00.1424770727 ), and which is Yours
I see now Lars has answered, so I would go with his answer.
Have a Nice Day
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Using a subquery with ordering was also my first try.
Unfortunately I have seen cases, where the optimizer decided to transform the statement so that the inner query wouldn't feed the data sorted into the string_agg function.
If it works however, then it's way more flexible than creating a procedure.
And you're right: the question was really well prepared and deserved the thumbs up!
- Lars
Sorting the list (or making it unique) in string_agg is not (yet) supported.
You can work out a workaround like this:
drop procedure list_users_sort;
create procedure list_users_sort (OUT user_sort TABLE (user_group NVARCHAR(20), user_name NVARCHAR(256)))
language sqlscript
reads sql data with result view list_users_sorted
as
begin
user_sort = select case
when left (user_name, 4) = '_SYS' THEN 'SYSTEM_USER'
when left (user_name, 3) = 'SYS' THEN 'SYSTEM_USER'
else
'CUSTOM_USER'
end as user_group , user_name
from users
order by user_group, user_name;
end;
select user_group, string_agg( user_name, ', ') from
list_users_sorted
group by user_group;
USER_GROUP | STRING_AGG(USER_NAME,', ') |
CUSTOM_USER | ADMIN, DEVDUDE, PUBLICJOE |
SYSTEM_USER | SYS, SYSTEM, _SYS_AFL, _SYS_EPM, _SYS_REPO, _SYS_STATISTICS |
- Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Starting with SPS 10 it is now possible to specify ORDER BY for STRING_AGG. Example:
SELECT
X,
STRING_AGG(Y, ',' ORDER BY Y) Y
FROM
( SELECT '1000' X, 'C' Y FROM DUMMY UNION ALL
SELECT '1000' X, 'A' Y FROM DUMMY UNION ALL
SELECT '1000' X, 'B' Y FROM DUMMY UNION ALL
SELECT '2000' X, 'C' Y FROM DUMMY
)
GROUP BY
X
ORDER BY
X
User | Count |
---|---|
89 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.