5 Replies Latest reply: Aug 25, 2014 2:41 PM by Frank Kulash RSS

    SQL query

    user539616

      Hello all,

       

      I have 2 tables.

       

      1. per_assignments_x (assignment_id, job_id, person_id)

      2. per_jobs.(job_id)

       

      Table 1:

       

      A1, J1, P1

      A2, J2,P1

       

      Table2:

       

      J1

      J2

       

      Is it possible to join only these 2 tables and get the output as

       

      P1,J1,J2

       

      Thank You

      KK

        • 1. Re: SQL query
          Frank Kulash

          Hi,

           

          You can get results like that with or without joining the 2 tables.

          All the information seems to be in the per_assignments_x table, so you can do this:

           

          SELECT    person_id

          ,         LISTAGG (job_id, ', ') WITHIN GROUP (ORDER BY job_id)  AS job_id_list

          FROM      per_assignments_x

          GROUP BY  person_id

          ;

          but if you need the other table for some reason, then you can join it.

           

          I hope this answers your question.

          If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.

          Explain, using specific examples, how you get those results from that data.  Be precise; from your first message, I can't even tell how many columns you want in the result set.

          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

          See the forum FAQ: Re: 2. How do I ask a question on the forums?

          • 2. Re: SQL query
            user539616

            Hi Frank,

             

            Thank You for the response. It was just an example of the data i posted. If that was the data i have, how to tune this query?

            • 3. Re: SQL query
              Frank Kulash

              Hi,

              user539616 wrote:

               

              ... how to tune this query?

              See the forum FAQ: Re: 3. How to  improve the performance of my query? / My query is running slow.

              • 4. Re: SQL query
                user539616

                I mean how to write the query based on my initial request. I was trying to do that using PIVOT but not successful.

                • 5. Re: SQL query
                  Frank Kulash

                  Hi,

                   

                  user539616 wrote:

                   

                  I mean how to write the query based on my initial request. I was trying to do that using PIVOT but not successful.

                  If you want each job_id in a separate column, then you can use PIVOT, but unless you're getting XML output, you'll have to hard-code the number of columns.  That is, you can say that there will be up to 10 jobs per person.  If there happen to be few for any person (or all persons) then some of the columns will be NULL.  If there happen to be more than 10 for a given person, only 10 can be displayed, and the others will be ignored.  If that's what you want, post the information I mentioned in reply #1.  Post your best attempt using PIVOT, also.  You might have the solution already, except for some small, easily-fixed misunderstanding.