Starting from Oracle table row and column placement

When you are facing a table in the following format: NO is the ID of the person, MONEY is the income, DAY is the week (1 represents Monday, 7 represents Sunday ).

When you are facing a table in the following format: NO is the ID of the person, MONEY is the income, DAY is the week (1 represents Monday, 7 represents Sunday ).

To meet the reading habits, the final report should be in the following format:

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

Let's implement it step by step:

1. Use DECODE to convert behavior Columns

SQL:

Select no,
DECODE (DAY, 1, MONEY, '') DAY1,
DECODE (DAY, 2, MONEY, '') DAY2,
DECODE (DAY, 3, MONEY, '') DAY3
FROM TEMP

Result:

2. Group by NO field and change the column name

SQL:

Select no, MAX (DAY1), MAX (DAY2), MAX (DAY3)
FROM (select no,
DECODE (DAY, 1, MONEY, '') DAY1,
DECODE (DAY, 2, MONEY, '') DAY2,
DECODE (DAY, 3, MONEY, '') DAY3
From temp)
Group by no;

Result:

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

Difficulties:

1. DECODE default value settings

DECODE Syntax: 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 result is as follows ():

2. Set the column default value ('undefined' is displayed when the DAY value is 8 ')

SQL:

Select no, MONEY,
DECODE (DAY, 1, 'mon ', 2, 'tue', 3, 'thr', 'undefined') DAY
FROM TEMP

Result:

3. row-column conversion consumes a large amount of data in the form.

Cause:

1. High time overhead of scanning target data.

2. multi-row merging due to data redundancy during group.

Advantages:

Next decode () function usage tips