Statement of Oracle row-to-column Conversion

Statement for Oracle row-to-column conversion:

/*

Drop table foo;

*/

/** Create a demo table **/

Create table foo (

Bbbid number (2), -- primary key

Depid number (2), -- unit NUMBER

AAC006 VARCHAR2 (1) -- Gender 1 male 2 female 0 unknown

CONSTRAINT CK_AAC006 CHECK (AAC006 = '1' OR AAC006 = '2' OR AAC006 = '0 ')

);

/** Insert data for testing **/

Insert into foo values (1, 1, '1 ');

Insert into foo values (2, 1, '0 ');

Insert into foo values (3,1, '2 ');

Insert into foo values (4,1, '2 ');

Insert into foo values (5, 2, '1 ');

Insert into foo values (6, 2, '0 ');

Insert into foo values (7,2, '2 ');

Insert into foo values (8, 2, '2 ');

Insert into foo values (9, 3, '2 ');

Insert into foo values (10, 3, '2 ');

Insert into foo values (11,3, '2 ');

/**

Objective: To perform row-to-column conversion on the table, you need to obtain the following result set:

Unknown depid male and female

1 1  1 2

2 1  1 2

3 0  0 3

*/

/** Raw data **/

SELECT * from foo;

/** Convert FOO. AAC006 to a value after three columns based on the value of the gender code table **/

Select depid,

DECODE (AAC006, '0', 1, 0) "male ",

DECODE (AAC006, '1', 1, 0) "female ",

DECODE (AAC006, '2', 1, 0) "unknown"

From foo;

/** Perform processing on the previous result set to get the row-to-column conversion result! **/

Select depid,

SUM (DECODE (AAC006, '0', 1, 0) "male ",

SUM (DECODE (AAC006, '1', 1, 0) "female ",

SUM (DECODE (AAC006, '2', 1, 0) "unknown"

FROM FOO

Group by depid;

/**

Summary (czw 20120624 ):

Row-to-column splitting refers to splitting a field into several fields according to the code value,Finally, the clustering function is used to obtain the corresponding value.

**/