Results 1 to 12 of 12

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

  1. #1
    Guest
    Guest

    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


  2. #2
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

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

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


  3. #3
    Guest
    Guest

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

    "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.


  4. #4
    Guest
    Guest

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

    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.


  5. #5
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

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

    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)


  6. #6
    Guest
    Guest

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

    Quote Originally Posted by bhaski View Post
    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!


  7. #7
    Guest
    Guest

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

    Quote Originally Posted by Guest View Post
    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 !


  8. #8
    Contributing Member
    Join Date
    Jun 2007
    Answers
    65

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

    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


  9. #9
    Guest
    Guest

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

    Quote Originally Posted by vikasvaidya View Post
    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


  10. #10
    Guest
    Guest

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

    Quote Originally Posted by vikasvaidya View Post
    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.


  11. #11
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

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

    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


  12. #12
    Contributing Member
    Join Date
    Jun 2007
    Answers
    65

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

    Quote Originally Posted by Guest View Post
    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact