Recently read some lines of the materials listed, but at the statement is not very good, I hope god can give me on the principle of the downward turn column, best can point in detail, combined with example would be even better

CodePudding user response:

Generally is the Max + case when (decode) to implement,
There is also a pivot

CodePudding user response:

Example of the building Lord read, there is?

Think of where you see examples of post, don't understand, can be targeted questions

CodePudding user response:

reference 1st floor js14982 response:

general is Max + case when (decode) to implement,
There is also a pivot

the pivot is 11 g functions that it

CodePudding user response:

Simply speaking, is a process of aggregation

CodePudding user response:

The principle of transfer line column, for example, with the result set:

Students subject
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Zhang SAN Chinese 90
Zhang SAN mathematics 102
Zhang SAN English 70
Li si Chinese 86
Li si math 92
Li si English 95

To get the result set below:
Students of Chinese maths English
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Zhang, 90, 102, 70
Li si, 86, 92, 95

This is the effect of the transfer line column

Analysis: in fact is to keep students main properties, the Chinese, math, English scores in all the subjects to extract a record on
If from the original implementation, without packaging function, is to use group by group the sum and decode to cooperate to complete the

Select the xs, sum (decode (km, 'language', cj, 0)) as yw, sum (decode (km, 'mathematics', cj, 0)) as sx, sum (decode (km,' English ', cj, 0)) as yy
The from t
Group by xs

In more than 11 g version encapsulates the pivot function can also achieve this effect

CodePudding user response:

Turn - line column
- data preparation
The create table test_xz
(ID varchar (20),
The name varchar (20)
);

The create table test_xz1
(
ID varchar (20),
Course varchar (20)
);

Insert into test_xz values (' 1 ', ', ');
Insert into test_xz values (' 2 ', 'Anna');
Insert into test_xz values (' 3 ', ', ');

Insert into test_xz1 values (' 1 ', 'mathematics');
Insert into test_xz1 values (' 1 ', 'Chinese');
Insert into test_xz1 values (' 2 ', 'mathematics');
Insert into test_xz1 values (' 2 ', 'Chinese');
Insert into test_xz1 values (' 2 ', 'English');
Insert into test_xz1 values (' 3 ', 'mathematics');
Insert into test_xz1 values (' 3 ', 'geographic');

- the method one:
The select t.i d, t.n ame, listagg (t1) course, ', ') within group (order by t.n ame) as cc
The from test_xz t, test_xz1 t1
Where t.i d=t1. Id
Group by t.i d, t.n ame

- the method 2:
The select t.i d, t.n ame, to_char (wm_concat (t1) course)) as the cc
The from test_xz t, test_xz1 t1
Where t.i d=t1. Id
Group by t.i d, t.n ame

CodePudding user response:

Recently wrote a C4.5 SQL data mining algorithm, useful to, are interested can refer to the
Thinking in four of the SQL series: C4.5 decision tree algorithm in data mining