CODEWITHSUNDEEP
row-numberarray-agg
Gopal.anant
Gopal.anant

Reputation: 1

row_number could not be used in array_to_string(array_to_agg function

I've emp table which contains name & name2 field. I need the output in a single line. As i dont have unique id(emp_id) in this table, i am getting error as below :-

ERROR: aggregate function calls cannot contain window function calls LINE 1: ...elect array_to_string(array_agg(concat(name,name2,row_number...

Query used :- select array_to_string(array_agg(concat(name,name2,row_number() over())),'') from emp

Expected result :-

Gopal---Anantha---1Radha---Meenakshi---2

Please help me

create table emp(name varchar(10), name2 varchar(10));

insert into emp values('Gopal','Anantha');

insert into emp values('Radha','Meenakshi');

Upvotes: -4

Views: 30

Answers (1)

Gopal.anant
Gopal.anant

Reputation: 1

I found the solution

select array_to_string(array_agg(concat(CASE WHEN a.rn = 1 THEN 'NEW' ELSE a.name END,a.name2)),'') from (
 select row_number() over() rn,name,name2 from emp) a

Result is :- NEWAnanthaRadhaMeenakshiKolahalanGopal

Upvotes: 0

Related Questions