Simple row-to-column conversion for Oracle

Make a report as required

Time two-color ball, winning .....

200905 sales

200906 sales

200907 sales

200908 sales

 

The result of an SQL statement is:

F_yearmonth, lot_name, money

200905 dual-color ball 50

200905 wins/loses 100

200906 dual-color ball 60

200906 wins/loses 70

 

 

Select f_yearmonth,
Max (decode (f_lotname, 'two-color ball', f_money, 0) as "two-color ball ",
Max (decode (f_lotname, 'lucky 3D ', f_money, 0) as "Lucky 3D ",
Max (decode (f_lotname, 'Winning color', f_money, 0) as "winning color ",
Max (decode (f_lotname, 'hour color ', f_money, 0) as "hour color ",
Max (decode (f_lotname, 'Time time happy ', f_money, 0) as "time happy ",
From
T_tmp_test_a
Group by f_yearmonth
Order by f_yearmonth

 

Key statement:

Max (decode (f_lotname, 'two-color ball', f_money, 0) as "two-color ball ",

Group by f_yearmonth

 

There should be only one reassembly by time and only one data record: 200905 dual-color ball 50

Then, if Max is used to retrieve a piece of data, 50 is used.

Decode indicates that if it is a 'two-color ball', take the corresponding f_money and place it on the column named "two-color ball.

 

Obviously, this column is fixed!