From the Oracle table to the list of the rows 1th/2 page _oracle

NO	Money	Day
1	23	1
1	43	2
1	-45	3
2	42	1
2	-10	2
2	50	3
3	100	8

In order to conform to the reading habits, the final report is expected to be the following format:

NO	MON	TUE	THR
1	23	43	-45
2	42	-10	50
3

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

Let's do this step-by-step:

1. Using the Decode Conversion behavior column

Sql:

SELECT NO,
DECODE (Day,1,money, ") DAY1,
DECODE (Day,2,money, ") DAY2,
DECODE (Day,3,money, "") DAY3
From TEMP

Results:

NO	DAY1	DAY2	DAY3
1	23
1		43
1			-45
2	42
2		-10
2			50
3

2. Group by No field and change the column name

Sql:

SELECT NO, Max (DAY1) MON, Max (DAY2) TUE, Max (DAY3) THR
From (SELECT NO,
DECODE (day, 1, Money, ') DAY1,
DECODE (day, 2, Money, ') DAY2,
DECODE (Day, 3, Money, ") DAY3
From TEMP)
GROUP by NO;

Results:

NO	MON	TUE	THR
1	23	43	-45
2	42	-10	50
3

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

Summary of the important points:

1.DECODE Default Value setting

The decode syntax is as follows: Decode (condition, value 1, translation value 1, value 2, translation value 2,... Value n, translation value n, default value)

If the default value is changed from ' (two single quotes) to 0, that is, sql:

SELECT NO, Max (DAY1) MON, Max (DAY2) TUE, Max (DAY3) THR
From (SELECT NO,
DECODE (day, 1, money,0) DAY1,
DECODE (Day, 2, money,0) DAY2,
DECODE (Day, 3, money,0) DAY3
From TEMP)
GROUP by NO;

The results are as follows ( all values are negative and null values are assigned to 0):

NO	MON	TUE	THR
1	23	43	0
2	42	0	50
3	0	0	0

2. Column default setting (the day value of 8 is displayed as ' undefined ')

Sql:

SELECT No,money,
DECODE (day,1, ' MON ', 2, ' TUE ', 3, ' THR ', ' undefined ') day
From TEMP

Results:

NO	Money	Day
1	23	MON
1	43	TUE
1	-45	THR
2	42	MON
2	-10	TUE
2	50	THR
3	100	Undefined

3. Row and column conversion in the form of large amount of data in the case of large consumption

Reason:

1. Scanning target data has a large time overhead.

Multi-line merging of data redundancy resulting from 2.GROUP by.

Advantages:

Table structure is stable: Day adds new values simply by adding records, without adding new columns!

Next page decode () tips for using a function

Current 1/2 page 12 Next read the full text