1 2 Previous Next 16 Replies Latest reply on Apr 24, 2019 9:21 AM by Stefan Jager

    Swap returned rows to columns

    3574931

      Looking for some SQL expertise to assist with my dilemma. I have a query below the generates the four rows shown below the query. I need a way to get the four rows into a single row (shown at bottom), with column headers of LOAD_ID, DRIVER(S), TRACTOR, & TRAILER.

       

      SELECT

      LL.LD_LEG_ID LOAD_ID,

      DA.DRVR_CD,

      DR.DRVR_NAME

      FROM LD_LEG_T LL

      JOIN DRVR_ASSN_T DA on DA.LD_LEG_ID = LL.LD_LEG_ID

      JOIN DRVR_T DR ON DR.DRVR_CD = DA.DRVR_CD

      WHERE LL.LD_LEG_ID = 1614

       

      LOAD_ID

      DRVR_CD
      DRVR_NAME

      1614

      DRIVER_007Riggs, Martin
      1614DRIVER_008Murtaugh, Roger
      1614TRACTOR_008TRACTOR 001
      1614TRAILER_001TRAILER 001

       

      This is how the data needs to look:

            

       

      LOAD_ID

       

      DRIVER(S)

       

      TRACTOR

       

      TRAILER

      1614Riggs, Martin;Murtaugh, RogerTractor 001Trailer 001
        • 1. Re: Swap returned rows to columns
          John Thorton

          3574931 wrote:

           

          Looking for some SQL expertise to assist with my dilemma. I have a query below the generates the four rows shown below the query. I need a way to get the four rows into a single row (shown at bottom), with column headers of LOAD_ID, DRIVER(S), TRACTOR, & TRAILER.

           

          SELECT

          LL.LD_LEG_ID LOAD_ID,

          DA.DRVR_CD,

          DR.DRVR_NAME

          FROM LD_LEG_T LL

          JOIN DRVR_ASSN_T DA on DA.LD_LEG_ID = LL.LD_LEG_ID

          JOIN DRVR_T DR ON DR.DRVR_CD = DA.DRVR_CD

          WHERE LL.LD_LEG_ID = 1614

           

          LOAD_ID

          DRVR_CD
          DRVR_NAME

          1614

          DRIVER_007Riggs, Martin
          1614DRIVER_008Murtaugh, Roger
          1614TRACTOR_008TRACTOR 001
          1614TRAILER_001TRAILER 001

           

          This is how the data needs to look:

           

           

          LOAD_ID

           

          DRIVER(S)

           

          TRACTOR

           

          TRAILER

          1614Riggs, Martin;Murtaugh, RogerTractor 001Trailer 001

          Please click on URL below & provide details as stated in #5 - #9 inclusive

           

          How do I ask a question on the forums?

           

          How do I convert rows to columns?


          • 2. Re: Swap returned rows to columns
            Frank Kulash

            Hi,

            3574931 wrote:

             

            Looking for some SQL expertise to assist with my dilemma. I have a query below the generates the four rows shown below the query. I need a way to get the four rows into a single row (shown at bottom), with column headers of LOAD_ID, DRIVER(S), TRACTOR, & TRAILER.

             

            SELECT

            LL.LD_LEG_ID LOAD_ID,

            DA.DRVR_CD,

            DR.DRVR_NAME

            FROM LD_LEG_T LL

            JOIN DRVR_ASSN_T DA on DA.LD_LEG_ID = LL.LD_LEG_ID

            JOIN DRVR_T DR ON DR.DRVR_CD = DA.DRVR_CD

            WHERE LL.LD_LEG_ID = 1614

             

            LOAD_ID

            DRVR_CD
            DRVR_NAME

            1614

            DRIVER_007Riggs, Martin
            1614DRIVER_008Murtaugh, Roger
            1614TRACTOR_008TRACTOR 001
            1614TRAILER_001TRAILER 001

             

            This is how the data needs to look:

             

             

            LOAD_ID

             

            DRIVER(S)

             

            TRACTOR

             

            TRAILER

            1614Riggs, Martin;Murtaugh, RogerTractor 001Trailer 001

            Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
            Also post the exact results you want from that data, and an explanation (in general terms) of how you get those results from that data, with specific examples.
            It always helps to show what you've tried, that is, the code that seems to come closest to what you want.

            Always say which version of Oracle you're using (for example, 12.2.0.1.0).
            See the Forum FAQ: Re: 2. How do I ask a question on the forums?

             

            What are the possible values of drvr_cd?  Are the 4 values shown above the only ones possible.

            Will you sometimes want a delimited list in the tractor or trailor columns?  If so, include an example when you post the sample data.

            What is unique in the results you are getting now.  (In this small sample, everything except load_id is unique, but I'll bet that's not true in your real data.)  How do you want to handle duplicates (if hey are possible)?

            • 3. Re: Swap returned rows to columns
              3574931

              Thanks for the feedback, I'll try to get better in the future. To answer your questions specifically, in this example, there will always only be a single tractor, and a single trailer, but there may be 1-3 drivers that drive the truck. The result that I want is shown at the bottom of my post, where the DRIVER(S) is the only field that would concatenate multiple driver's names into a single column

              • 4. Re: Swap returned rows to columns
                jaramill

                Well the future is now, so post the full Oracle database version that is referenced in #5 of the link John and Frank mentioned as well as the other things mentioned.

                Remember this is not a free-coding service.  You need to provide the info in order to get help.  This a volunteer forum.  Provide the DDL and DML statements of the table and data.

                You did that but not in a way where forum members can copy/paste into their own db environment, and if you are on an older version of Oracle (which we do not know since you FAILED to provide this info)

                then certain solutions (i.e. PIVOT/UNPIVOT) won't work.

                 

                Help US help YOU.

                • 5. Re: Swap returned rows to columns
                  CarlosDLG

                  You still need to post your tables' and sample data script, if you want tested suggestions.

                   

                  When you post it, please explain, just based on the data, how you know that a trailer or a tractor is not a driver.

                  • 6. Re: Swap returned rows to columns
                    Frank Kulash

                    Hi,

                     

                    Okay, so a given load_id can be on any number of rows where drvr_cd starts with 'DRIVER_', but (at most) only one row where drvr_cd starts with 'TRACTOR_' or 'TRAILER_',  You want to display one row per load_id, with a semicolon-delimited list of all the drivers in one column, and the tractor and the trailer in additional columns.

                     

                    Since I don't have a copy of your table, I'll show you how to do that using the scott.emp table, where a given deptno can be on any number of rows with job='ANALYST', but (at most) only one row where job is 'MANAGER' or 'PRESIDENT'.  The query below displays one row per deptno, with a semicolon-delimited list of all the analysts in one column, and the manager and the president in additional columns.

                    SELECT    deptno

                    ,         LISTAGG ( CASE

                                            WHEN  job = 'ANALYST'

                                            THEN  ename

                                        END

                                      , ';'

                                      ) WITHIN GROUP (ORDER BY  ename)  AS analyst

                    ,         MIN ( CASE

                                        WHEN  job = 'MANAGER'

                                        THEN  ename

                                    END

                                  )                                     AS manager

                    ,         MIN ( CASE

                                        WHEN  job = 'PRESIDENT'

                                        THEN  ename

                                    END

                                  )                                     AS president

                    FROM       scott.emp

                    WHERE      deptno  IN (10, 20)

                    GROUP BY   deptno

                    ORDER BY   deptno

                    ;

                    Output:

                    DEPTNO ANALYST    MANAGER    PRESIDENT

                    ------- ---------- ---------- ----------

                         10            CLARK      KING

                         20 FORD;SCOTT JONES

                    No error occurs just because a particular deptno is missing a particular job.  For example, deptno=10 has no 'ANALYST', so the analyst column on that row in NULL.

                     

                    This query will work in Oracle 11.2 or higher.  In versions 9.1 through 11.1 you can't use LISTAGG, but there are other ways to get the same results as LISTAGG.

                    • 7. Re: Swap returned rows to columns
                      3574931

                      Sorry all, the permissions I have for the DB don't allow me to create DDL and DML statements of the table and data, all I can do is query the tables which is what I listed previously. We're running Oracle 11g.

                      • 8. Re: Swap returned rows to columns
                        Frank Kulash

                        Hi,

                        3574931 wrote:

                         

                        Sorry all, the permissions I have for the DB don't allow me to create DDL and DML statements of the table and data, all I can do is query the tables which is what I listed previously.

                        That's a shame.  If your job involves writing queries, you really ought to have the ability to create your own test tables, in your own schema in a Development database.

                         

                        But, if you can't create tables, you can always use WITH clauses instead, as shown in the Forum FAQ: Re: 2. How do I ask a question on the forums?  under "7) Sample Data".

                         

                         

                        We're running Oracle 11g.

                        There is no Oracle 11f or 11h, so it's pretty silly to say you're running 11g, especially after reply #6, which said the difference between 11.1 and 11.2 was significant in this case.

                        • 10. Re: Swap returned rows to columns
                          3574931

                          Frank, I tried using your example as such but I am getting an error saying there's a missing parenthesis.

                           

                          SELECT    LL.LD_LEG_ID LOAD_ID

                          , LISTAGG ( CASE WHEN DA.DRVR_CD LIKE 'DRIVER%' THEN  DR.DRVR_NAME END, ';' ) WITHIN GROUP (ORDER BY  DR.DRVR_NAME) AS DRIVER

                          , MIN ( CASE WHEN  DA.DRVR_CD LIKE 'TRACTOR%' THEN  DR.DRVR_NAME END) AS TRACTOR

                          , MIN ( CASE WHEN  DA.DRVR_CD LIKE 'TRAILER%' THEN  DR.DRVR_NAME END) AS TRAILER

                          FROM LD_LEG_T LL

                          JOIN DRVR_ASSN_T DA on DA.LD_LEG_ID = LL.LD_LEG_ID

                          JOIN DRVR_T DR ON DR.DRVR_CD = DA.DRVR_CD

                          WHERE LL.LD_LEG_ID IN (1614,1615)

                          GROUP BY LL.LD_LEG_ID

                          • 11. Re: Swap returned rows to columns
                            Frank Kulash

                            Hi,

                             

                            3574931 wrote:

                             

                            Frank, I tried using your example as such but I am getting an error saying there's a missing parenthesis.

                             

                            SELECT LL.LD_LEG_ID LOAD_ID

                            , LISTAGG ( CASE WHEN DA.DRVR_CD LIKE 'DRIVER%' THEN DR.DRVR_NAME END, ';' ) WITHIN GROUP (ORDER BY DR.DRVR_NAME) AS DRIVER

                            , MIN ( CASE WHEN DA.DRVR_CD LIKE 'TRACTOR%' THEN DR.DRVR_NAME END) AS TRACTOR

                            , MIN ( CASE WHEN DA.DRVR_CD LIKE 'TRAILER%' THEN DR.DRVR_NAME END) AS TRAILER

                            FROM LD_LEG_T LL

                            JOIN DRVR_ASSN_T DA on DA.LD_LEG_ID = LL.LD_LEG_ID

                            JOIN DRVR_T DR ON DR.DRVR_CD = DA.DRVR_CD

                            WHERE LL.LD_LEG_ID IN (1614,1615)

                            GROUP BY LL.LD_LEG_ID

                            Always post the exact error message.  It probably has additional useful information, in particular about exactly where the error was detected.  If you'd post some sample data, I could run the query myself, and get the full error message.

                             

                            Error messages often say "Missing parentheses" for all kinds of syntax errors;

                            • 12. Re: Swap returned rows to columns
                              jaramill

                              3574931 wrote:

                               

                              Frank, I tried using your example as such but I am getting an error saying there's a missing parenthesis.

                               

                              SELECT LL.LD_LEG_ID LOAD_ID

                              , LISTAGG ( CASE WHEN DA.DRVR_CD LIKE 'DRIVER%' THEN DR.DRVR_NAME END, ';' ) WITHIN GROUP (ORDER BY DR.DRVR_NAME) AS DRIVER

                              , MIN ( CASE WHEN DA.DRVR_CD LIKE 'TRACTOR%' THEN DR.DRVR_NAME END) AS TRACTOR

                              , MIN ( CASE WHEN DA.DRVR_CD LIKE 'TRAILER%' THEN DR.DRVR_NAME END) AS TRAILER

                              FROM LD_LEG_T LL

                              JOIN DRVR_ASSN_T DA on DA.LD_LEG_ID = LL.LD_LEG_ID

                              JOIN DRVR_T DR ON DR.DRVR_CD = DA.DRVR_CD

                              WHERE LL.LD_LEG_ID IN (1614,1615)

                              GROUP BY LL.LD_LEG_ID

                              Show US the error.  Run that query in SQL* Plus and it will show where the issue is

                              • 13. Re: Swap returned rows to columns
                                3574931

                                RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.

                                UDA-SQL-0107 A general exception has occurred during the operation "prepare".

                                ORA-00907: missing right parenthesis

                                 

                                 

                                RSV-SRV-0042 Trace back:

                                RSReportService.cpp(736): QFException: CCL_CAUGHT: RSReportService::processImpl()

                                RSReportServiceMethod.cpp(253): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_Request

                                RSASyncExecutionThread.cpp(873): QFException: RSASyncExecutionThread::checkException

                                RSASyncExecutionThread.cpp(317): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_Request

                                RSASyncExecutionThread.cpp(918): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_Request

                                RSRequest.cpp(1657): QFException: CCL_THROW: RSRequest::executeInteractivePrompting()

                                RSQueryMgr.cpp(808): QFException: CCL_RETHROW: RSQueryMgr::getListIterator

                                RSQueryMgr.cpp(880): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIterator

                                RSQueryMgr.cpp(988): QFException: CCL_RETHROW: RSQueryMgr::createIterator

                                RSQueryMgrBasic.cpp(296): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommand

                                RSQueryMgrBasic.cpp(673): QFException: CCL_THROW: RSQueryMgrBasic::processException

                                RSQueryMgrExecutionHandlerImpl.cpp(168): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()

                                RSFaultHandler.cpp(97): QFException: CCL_THROW: RSFaultHandler::handleNonCriticalException

                                QFSSession.cpp(1190): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()

                                QFSSession.cpp(1188): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()

                                QFSSession.cpp(1145): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()

                                QFSSession.cpp(1121): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()

                                QFSConnection.cpp(888): QFException: CCL_RETHROW: QFSConnection::Execute

                                QFSQuery.cpp(311): QFException: CCL_RETHROW: QFSQuery::Execute v2

                                CoordinationQFSQuery.cpp(4532): QFException: CCL_THROW: CoordinationPlanner

                                • 14. Re: Swap returned rows to columns
                                  CarlosDLG

                                  That is not useful, unfortunately, because that is the error stack generated by the application from which you are executing the query.

                                   

                                  You should try to make the query work in SQL Developer or SQL*Plus first, and only after you have a working statement, try to integrate into your application.

                                   

                                  As Jaramill already suggested, run the query from SQL*Plus (or SQL Developer) and post here the complete error message you get.  If you don't get an error when running it from there, then the problem is not in the query, but in the application or the driver you are using to connect to the database.

                                  1 2 Previous Next