4 Replies Latest reply: Dec 28, 2012 2:44 PM by 743517 RSS

    Transpose rows into columns

    743517
      with t as
      (select 'Global' as ASSET_TYPE, NULL As ASSET_MASTER,NULL AS MILES_YARDS_FORMAT,
      'NO PARENT ASSET FOUND' AS MSG_NO_PARENT,
      'ASSET NOT WITHIN PARENT EXTENTS' AS MSG_OUT_OF_BOUNDS,
      'MILES_FROM GREATER THAN MILES_TO' AS MSG_START_GT_END,
      'ASSET HAS ZERO LENGTH' AS MSG_ZERO_LENGTH,
      'OVERLAPPING ASSET - NOT CONTINUOUS EXTENTS' AS MSG_OVERLAP,
      'ONE OR MORE KEY ATTRIBUTES CONTAIN NULL VALUE' AS MSG_NULL_VALUES from dual union all
      select 'Ballast','XDF','000.0000',NULL,NULL,NULL,NULL,NULL,NULL from dual )
      select * from t

      I want the out put the above data to be transposed into single row. Null values should not be displayed.


      Regards
      P
        • 1. Re: Transpose rows into columns
          Manik
          Can you check the output of this query and let us know if it matches your requirement?
          WITH t AS
                  (SELECT 'Global' AS ASSET_TYPE,
                          NULL AS ASSET_MASTER,
                          NULL AS MILES_YARDS_FORMAT,
                          'NO PARENT ASSET FOUND' AS MSG_NO_PARENT,
                          'ASSET NOT WITHIN PARENT EXTENTS' AS MSG_OUT_OF_BOUNDS,
                          'MILES_FROM GREATER THAN MILES_TO' AS MSG_START_GT_END,
                          'ASSET HAS ZERO LENGTH' AS MSG_ZERO_LENGTH,
                          'OVERLAPPING ASSET - NOT CONTINUOUS EXTENTS' AS MSG_OVERLAP,
                          'ONE OR MORE KEY ATTRIBUTES CONTAIN NULL VALUE'
                             AS MSG_NULL_VALUES
                     FROM DUAL
                   UNION ALL
                   SELECT 'Ballast',
                          'XDF',
                          '000.0000',
                          NULL,
                          NULL,
                          NULL,
                          NULL,
                          NULL,
                          NULL
                     FROM DUAL)
          SELECT listagg (asset_type, '') WITHIN GROUP (ORDER BY 1) ASSET_TYPE,
                 MAX (ASSET_MASTER) ASSET_MASTER,
                 MAX (MILES_YARDS_FORMAT) MILES_YARDS_FORMAT,
                 MAX (MSG_NO_PARENT) MSG_NO_PARENT,
                 MAX (MSG_OUT_OF_BOUNDS) MSG_OUT_OF_BOUNDS,
                 MAX (MSG_START_GT_END) MSG_START_GT_END,
                 MAX (MSG_ZERO_LENGTH) MSG_ZERO_LENGTH,
                 MAX (MSG_OVERLAP) MSG_OVERLAP,
                 MAX (MSG_NULL_VALUES) MSG_NULL_VALUES
            FROM t;
          Cheers,
          Manik.
          • 2. Re: Transpose rows into columns
            ranit B
            Manik,

            LISTAGG will not work below 11g...

            Please Refer : <b>String Aggregation Techniques</b>

            Try this -
            WITH t AS
                    (SELECT 'Global' AS ASSET_TYPE,
                            NULL AS ASSET_MASTER,
                            NULL AS MILES_YARDS_FORMAT,
                            'NO PARENT ASSET FOUND' AS MSG_NO_PARENT,
                            'ASSET NOT WITHIN PARENT EXTENTS' AS MSG_OUT_OF_BOUNDS,
                            'MILES_FROM GREATER THAN MILES_TO' AS MSG_START_GT_END,
                            'ASSET HAS ZERO LENGTH' AS MSG_ZERO_LENGTH,
                            'OVERLAPPING ASSET - NOT CONTINUOUS EXTENTS' AS MSG_OVERLAP,
                            'ONE OR MORE KEY ATTRIBUTES CONTAIN NULL VALUE'
                               AS MSG_NULL_VALUES
                       FROM DUAL
                     UNION ALL
                     SELECT 'Ballast',
                            'XDF',
                            '000.0000',
                            NULL,
                            NULL,
                            NULL,
                            NULL,
                            NULL,
                            NULL
                       FROM DUAL)
            SELECT /* listagg (asset_type, '') WITHIN GROUP (ORDER BY 1) ASSET_TYPE, */
                   RTRIM(XMLAgg(XMLElement(e, asset_type||', ')).EXTRACT('//text()'),', ') ASSET_TYPE,
                   MAX (ASSET_MASTER) ASSET_MASTER,
                   MAX (MILES_YARDS_FORMAT) MILES_YARDS_FORMAT,
                   MAX (MSG_NO_PARENT) MSG_NO_PARENT,
                   MAX (MSG_OUT_OF_BOUNDS) MSG_OUT_OF_BOUNDS,
                   MAX (MSG_START_GT_END) MSG_START_GT_END,
                   MAX (MSG_ZERO_LENGTH) MSG_ZERO_LENGTH,
                   MAX (MSG_OVERLAP) MSG_OVERLAP,
                   MAX (MSG_NULL_VALUES) MSG_NULL_VALUES
              FROM t;
            gives
            Global, Ballast     XDF     000.0000     NO PARENT ASSET FOUND     ASSET NOT WITHIN PARENT EXTENTS     MILES_FROM GREATER THAN MILES_TO     ASSET HAS ZERO LENGTH     OVERLAPPING ASSET - NOT CONTINUOUS EXTENTS     ONE OR MORE KEY ATTRIBUTES CONTAIN NULL VALUE
            Edited by: ranit B on Dec 28, 2012 12:23 PM
            -- o/p added
            • 3. Re: Transpose rows into columns
              Manik
              Ranit,

              agreed, assumed OP is in 11g enviroment. :)

              Still OP has to confirm both our assumptions and answers are correct though ;)

              Cheers,
              Manik.
              • 4. Re: Transpose rows into columns
                743517
                Asset type data should split in two columns.It should not come in one column as it is appearing.

                Advice is appreciated how to tweak that query.

                General if data is present in both the rows it should appear as two column
                Regards
                P

                Edited by: user5698021 on Dec 28, 2012 12:43 PM