The instance resolution and SQL horizontal display are displayed in the row-to-column manner of SQL query statements.

The instance resolution and SQL horizontal display are displayed in the row-to-column manner of SQL query statements.

This article shares two examples of horizontal display of rows and columns in SQL query statements for your reference. The specific content is as follows:

Example 1:

In the SQL query statement row-to-column horizontal display, there is no CASE in access, instead of using IIF

Select iif (sex = '1', 'male', 'female ') from tablename

select country, sum(case when type='A' then money end) as A,sum(case when type='B' then money end) as B,sum(case when type='C' then money end) as Cfrom table1group by country

Example 2:

/* Problem: Assume that there is a student renewal table (tb) as follows: name course score Zhang San Language 74 Zhang San mathematics 83 Zhang San physics 93 Li Si language 74 Li Si mathematics 84 Li Si physics 94 want to change (the following result is obtained ): name Chinese mathematics physics ---- Li Si 74 84 94 Zhang San 74 83 93 ------------------- */create table tb (name varchar (10), course varchar (10), score int) insert into tb values ('zhang san', 'China', 74) insert into tb values ('zhang san', 'mat', 83) insert into tb values ('zhang san ', 'Physical ', 93) insert into tb values ('Li si', 'China', 74) insert into tb values ('Li 4', 'mat', 84) insert into tb values ('lily', 'Physical ', 94) go -- SQL server 2000 static SQL, only Chinese, mathematics, and physics courses are required. (The same as below) select name as name, max (case course when 'chine' then score else 0 end) language, max (case course when 'mate' then score else 0 end) mathematics, max (case course when 'physical 'then score else 0 end) Physical from tbgroup by name -- SQL server 2000 dynamic SQL, refers to the course more than three courses: Chinese, mathematics, and physics. (The same as below) declare @ SQL varchar (8000) set @ SQL = 'select name' select @ SQL = @ SQL + ', max (case course when''' + course + ''' then score else 0 end) ['+ course +'] 'from (select distinct course from tb) as aset @ SQL = @ SQL + 'from tb group by name' exec (@ SQL) -- SQL server 2005 static SQL. Select * from (select * from tb) a between (max (score) for course in (language, mathematics, physics) B -- SQL server 2005 dynamic SQL. Declare @ SQL varchar (8000) select @ SQL = isnull (@ SQL + '], [', '') + course from tb group by course set @ SQL = '[' + @ SQL + ']' exec ('select * from (select * from tb) a round (max (score) for course in ('+ @ SQL +') B ') -----------------------------------/* question: add the average score based on the above results and get the following result: name, Chinese Mathematics, physical average score ---- Li Si 74 84 94 84.00 252 Zhang San 74 83 93 83.33 250 */

The above is all the content of this article, hoping to help you learn.