Thread: What SQL statement can I use to produce this kind of output

Hello Gurus! My question is : I have a table, assuming it is an employee table with these columns : empno, empname, jobname with the following records :
0001, 'Roger','Programmer'
0002, 'Miriam','ETL Developer'
0001,'Roger','Analyst'
0003,'Roman','Encoder'
0002,'Miriam','Analyst'

What SQL statement can I use to produce this kind of output :

0001 Roger Analyst/Programmer
0002 Miriam Analyst/ETL Developer
0003 Roman Encoder

Meaning, if name or code is the same it should be in single record but should concatenate the jobname.... THANKS

BTW : I have already solved this using a cursor, but if there is another way i would appreciate it ... From Manila

use self join and distinct operator your problem will be solved.

"use self join and distinct operator your problem will be solved."

Brother, could you give me your exact statement that could concatenate the column from two records ( kindly re check my scenario ). I have tried using this self join sub query distinct, and did not work, that's the reason why I've used CURSOR. If you could post your statement, would be great.

Again, the result should be like this:

Col1 Col2 Col3
0001 Roger Analyst/Programmer
0002 Miriam Analyst/ETL Developer
0003 Roman Encoder

If you'll notice column3 is the result of two records from roger's job. If you could not give me your statement using self join and distinct, that means you are not sure of it. I would continue using the cursor that i've created. Thanks.

hi check it

10:27:21 SQL> select
10:27:23 2 empno,empname,
10:27:23 3 max(decode(rn, 1, jobname))||'/' col1,
10:27:23 4 max(decode(rn, 2, jobname)) col2
10:27:23 5 from (
10:27:23 6 select empno,empname,jobname, row_number() over (partition by empno order by empno) rn

10:27:23 7 from ab
10:27:23 8 order by empno)
10:27:23 9 group by empno,empname;

EMPNO EMPNAME COL1 COL2
---------- -------------------- ----------- ----------
0001 Roger Programer/ Analyst
0002 Miriam Developer/ Analyst
0003 Roman Encoder/

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 VIEW
3 2 WINDOW (SORT)
4 3 TABLE ACCESS (FULL) OF 'AB' (TABLE)



10:27:25 SQL> select *from ab;

EMPNO EMPNAME JOBNAME
---------- -------------------- ----------
0001 Roger Programer
0002 Miriam Developer
0001 Roger Analyst
0003 Roman Encoder
0002 Miriam Analyst

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'AB' (TABLE)

Originally Posted by bhaski

hi check it

10:27:21 SQL> select
10:27:23 2 empno,empname,
10:27:23 3 max(decode(rn, 1, jobname))||'/' col1,
10:27:23 4 max(decode(rn, 2, jobname)) col2
10:27:23 5 from (
10:27:23 6 select empno,empname,jobname, row_number() over (partition by empno order by empno) rn

10:27:23 7 from ab
10:27:23 8 order by empno)
10:27:23 9 group by empno,empname;

EMPNO EMPNAME COL1 COL2
---------- -------------------- ----------- ----------
0001 Roger Programer/ Analyst
0002 Miriam Developer/ Analyst
0003 Roman Encoder/

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 VIEW
3 2 WINDOW (SORT)
4 3 TABLE ACCESS (FULL) OF 'AB' (TABLE)



10:27:25 SQL> select *from ab;

EMPNO EMPNAME JOBNAME
---------- -------------------- ----------
0001 Roger Programer
0002 Miriam Developer
0001 Roger Analyst
0003 Roman Encoder
0002 Miriam Analyst

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'AB' (TABLE)

Hey Bhaski,

Wow what a great SQL statement, I have not tried it yet but it seems it will really produce the same result as expected. However, will this work if the records for let say for empno, empname is more than 2 should be dynamic
"max(decode(rn, 1, jobname))||'/' col1,10:27:23 4 max(decode(rn, 2, jobname)) col2" <--- since you've hardcoded it. I only want 1 column created for the jobname. Anyways, I'll this in our office tomorrow and will return to you. Thanks!

Originally Posted by Guest

Hey Bhaski,

Wow what a great SQL statement, I have not tried it yet but it seems it will really produce the same result as expected. However, will this work if the records for let say for empno, empname is more than 2 should be dynamic
"max(decode(rn, 1, jobname))||'/' col1,10:27:23 4 max(decode(rn, 2, jobname)) col2" <--- since you've hardcoded it. I only want 1 column created for the jobname. Anyways, I'll this in our office tomorrow and will return to you. Thanks!

Bhaski, yep it won't work in a multiple records (more than 2), because it is hardcoded unless I have to decode up to 100 records just in case. Though, it is faster than cursor right ? But I am afraid if it hits more than a hundred it will hit an error not capturing all. But this is really great. Thanks !

Hi,

The best way to fulfil your requirement is that first create a function, which will accept the empno and a separater string as input param and will return all the jobnames in seprated format in a single row.

I'll show u how:
create function getJobName(EmpNo varchar(5), Separator char(1))
as
begin
declare @retStr varchar(100)
set @retStr = ''
select @retStr = @retStr + jobname from employee where empno = EmpNo + Separator

return @retStr
end

Now, call it in ur query as
select distinct empno, empname, getJobName(empno, '/') from employee

Tell me, if u have any problem.

--V V--
Vikas Vaidya

Originally Posted by vikasvaidya

Hi,

The best way to fulfil your requirement is that first create a function, which will accept the empno and a separater string as input param and will return all the jobnames in seprated format in a single row.

I'll show u how:
create function getJobName(EmpNo varchar(5), Separator char(1))
as
begin
declare @retStr varchar(100)
set @retStr = ''
select @retStr = @retStr + jobname from employee where empno = EmpNo + Separator

return @retStr
end

Now, call it in ur query as
select distinct empno, empname, getJobName(empno, '/') from employee

Tell me, if u have any problem.

--V V--
Vikas Vaidya

Hi Vikas,

Thanks for the help, I guess you're wrote this SQL in T-SQL format right ? Anyways, in case I'll convert this to PL/SQL. But this is good, and from the logic I see it is right, nicely done. I'll try this tomorrow.

Thanks

Originally Posted by vikasvaidya

Hi,

The best way to fulfil your requirement is that first create a function, which will accept the empno and a separater string as input param and will return all the jobnames in seprated format in a single row.

I'll show u how:
create function getJobName(EmpNo varchar(5), Separator char(1))
as
begin
declare @retStr varchar(100)

set @retStr = ''
select @retStr = @retStr + jobname from employee where empno = EmpNo + Separator

return @retStr
end

Now, call it in ur query as
select distinct empno, empname, getJobName(empno, '/') from employee

Tell me, if u have any problem.

--V V--
Vikas Vaidya

Hi Vikas,

When I get into your getjobname function the statement is :

set @retStr = ''
select @retStr = @retStr + jobname from employee where empno = EmpNo + Separator

return @retStr
end

Have you tried running this statement, have you seen whether this concatenates every record's jobname for the particular employe, I guess no, it will only take 1 record. not this simple. Did not work.

hi

i saw your remark in delay.However i hve a query for you which is dynamic and think give u o/p as u wanted.See I read something for it discuss with others and wrote it.

here we go------->

select
cast(id as number) id,
name,
substr(max(substr(sys_connect_by_path(job, '/'), 2)), 1, 60) new_job
from (select id,
name,
job,
row_number() over(partition by id order by id) rn
from ab)
start with rn = 1
connect by prior rn+1 = rn
and prior id = id
group by id, name

o/p is ------------------------>

ID NAME NEW_JOB
--------------------------
1 Roger Programmer/Analyst
2 Miriam ETL Developer/Analyst
3 Roman Encoder

Originally Posted by Guest

Hi Vikas,

When I get into your getjobname function the statement is :

set @retStr = ''
select @retStr = @retStr + jobname from employee where empno = EmpNo + Separator

return @retStr
end

Have you tried running this statement, have you seen whether this concatenates every record's jobname for the particular employe, I guess no, it will only take 1 record. not this simple. Did not work.

Hi,

I have been try this and we are even using it on our projects that are running live and there is no problem.

Pls try to convert it into PL/SQL code. If it still doesn't work then may just because of difference in behavior of PL/SQL and TSQL.

If u still have problems, then pls inform me I'll see what i can do.

--V V--
Vikas Vaidya