AD

The columns of the same data grouped into different rows

 select 
        CARRIERACCOUNDCODE, 
        max(decode(sectionId, 1,sectionId)) id1, 
        max(decode(sectionId, 1, setionName)) name1,

        max(decode(sectionId, 2,sectionId)) id2, 
        max(decode(sectionId, 2, setionName)) name2 ,

        max(decode(sectionId, 3,sectionId)) id3, 
        max(decode(sectionId, 3, setionName)) name3,

        max(decode(sectionId, 4,sectionId)) id4,
        max(decode(sectionId, 4, setionName)) name4,
        
         max(decode(sectionId, 5,sectionId)) id5,
        max(decode(sectionId, 5, setionName)) name5,
        
         max(decode(sectionId, 6,sectionId)) id6,
        max(decode(sectionId, 6, setionName)) name6
from (
        select 
            CARRIERACCOUNDCODE, 
            setionName,
            sectionId,
            row_number() over(partition by CARRIERACCOUNDCODE order by sectionId) rn, 
            count(*) over(partition by CARRIERACCOUNDCODE) cnt 

        from (SELECT t2.CARRIERACCOUNDCODE,
                        t2.sectionId,
                        t2.setionName
                FROM    carrier t1
                     LEFT JOIN
                        funcinfo t2
                     ON t1.CARRIERACCOUNTCODE = t2.CARRIERACCOUNDCODE 
                     where CARRIERACCOUNDCODE is not null
                 )
        )
  
     group by CARRIERACCOUNDCODE;
     

标签: partition, cnt, id4
分类: Database
时间: 2010-12-14

相关文章

  1. The column of the same data grouped into different rows

    select CARRIERACCOUNDCODE, max(decode(sectionId, 1,sectionId)) id1, max(decode(sectionId, 1, setionName)) name ...
  2. Data Source Xiangjie

    First, the data source Description: ************************************************** *********************** ...
  3. Mixed data into Excel using the OleDbConnection loss of data analysis and solution column

    [Reproduced, the original source http://www.douban.com/note/18510346/] 1 Introduction <br /> in the appl ...
  4. Refresh GridView using Ajax partial simple implementation of data binding

    Many users have such needs, such as: Click the button to refresh the data in the GridView, not the page refres ...
  5. Massive data query optimization (fine)

    Database query plan optimization method Database system is the core of management information systems, databas ...
  6. Using SQL * LOADER, the text data into the database

    Using SQL * LOADER ... First, write a CTL file, result.ctl content: load data infile 'D: DATAFILE.TXT' into ta ...
  7. Oracle create user and data tables

    First, the concept of 1. Database (Database) What is a Database? Database is organized according to a data mod ...
  8. Oracle Database data type of the difference between the four char.varchar.varchar2.nvarchar

    the difference between varchar and varchar2 with the latter accounting for all the characters are two bytes, c ...
  9. Use the DB2 built-in functions quickly construct test data

    Use the DB2 built-in functions quickly construct test data [Case] using the DB2 built-in functions for rapid c ...
  10. Oracle data manipulation and control of language Xiangjie

    SQL language is divided into four categories: data query language DQL, data manipulation language DML, data de ...
  11. SQL Server data types

    SQL Server Standard data types data type Type Description bit integer whose value can only be 0,1 or NULL, suc ...
  12. Oracle created. Delete users and data tables

    2. Data table space (Tablespace) Always need space for storage of data, Oracle database by function, to a cert ...
  13. Change data type sql server 2005

    sql server 2005 sql 2000 data types and some differences, create table, the column data type points to note Us ...
  14. Transfer of type 1 rows: row after the data packet transfer out

    -- Xingzhuan column type a : Data grouped by xingzhuan column ( The value of each column in the same way ) -- ...
  15. arcengine raster data using summary

    arcengine raster data using summary 1, the raster data storage type can generally be stored as raster data ESR ...
  16. imp to import large data files

    In the use of exp, imp data transfer, as the scene of massive data may be in use when the export exp, exp will ...
  17. Oracle data import and export imp / exp command and grant command

    First, data import and export command Oracle data import and export imp / exp is equivalent to oracle data res ...
  18. Summary of the concept of Oracle data block (original)

    Oracle data within the logical storage Oracle stored data, the smallest particle size (finest level of granula ...
  19. Summary of the concept of Oracle data blocks

    Oracle data within the logical storage Oracle stored data, the smallest particle size (finest level of granula ...