Usage and comparison of case and decode in SQL and Oracle (Row-to-column conversion)

Reference:

For example, an External table is created here, as shown in:

Comparison:

1. In SQL, we can only use the case functions,CodeAnd the results are as follows:

Select name,
Case subject
When 'China' then 1
When 'mate' then 2
When 'English 'Then 3 -- else 3
End as 'subject Code'
From results

Similarly, we can use case to implement row-to-column conversion. The code and results are as follows:

Select name,
Sum (case when subject = 'China' then result end) 'China ',
Sum (case when subject = 'mate' then result end) 'mate ',
Sum (case when subject = 'English 'then result end)''
From results
Group by name

2. However, in Oracle, both functions can be used. The code and result are as follows:

Decode usage: Select name, decode (subject, '', 1, 'mat', 2, 'English', 3) Subject code from results;

Select name, decode (subject, 'China', 1, 'mat', 2, 3) Subject code from results;

Case usage:

Select name,
Case
When subject = 'China' then 1
When subject = 'mate' then 2
When subject = 'English 'Then 3 -- else 3
End as subject code
From results;

Both methods can achieve the same result:

Similarly, we can use these two functions to implement row-to-column conversion. The code and results are as follows:

Decode:

Select
Name,
Sum (decode (subject, 'China', result, 0) language,
Sum (decode (subject, 'mat', result, 0) mathematics,
Sum (decode (subject, 'English ', result, 0) English
From results
Group by name;

Case:

Select
Name,
Sum (case when subject = 'China' then result else 0 end) language,
Sum (case when subject = 'mate' then result else 0 end) mathematics,
Sum (case when subject = 'English 'Then result else 0 end) English
From results
Group by name;

 

Intermediate Results of database tables and row-to-column conversion (think about it or attach it)

ORACLE:

Select
Name,
Decode (subject, 'China', result, 0) language,
Decode (subject, 'mat', result, 0) mathematics,
Decode (subject, 'English ', result, 0) English
From results;

Or

Select
Name,
(Case when subject = 'China' then result else 0 end) language,
(Case when subject = 'mate' then result else 0 end) math,
(Case when subject = 'English 'Then result else 0 end) English
From results;

The result is as follows:

Correct the above content. This article is only intended to attract others...

(Continued)

Dynamic SQL in SQL2000

Declare @ SQL varchar (8000)
Set @ SQL = 'select name'
Select @ SQL = @ SQL + ', sum (case subject when' + char (39) + Subject + char (39) + 'then result else 0 end) ['+ Subject +']'
From (select distinct subject from results) as
Set @ SQL = @ SQL + 'from results group by name'
Exec (@ SQL)

 

Dynamic SQL in sql2005

Declare @ SQL varchar (8000)
Select @ SQL = isnull (@ SQL + '], [', '') + subject from results group by subject
Set @ SQL = '[' + @ SQL + ']'
Exec ('select * From results values (max (result) for subject in ('+ @ SQL +') B ')