Major software companies. NET Engineer's written questions about the list of the row and column conversion __.net

"Major software companies. NET Engineer's written question "------have to look

Overview of the major communities, forums, the major ORM framework fire, such as NHibernate, LINQ to SQL, ado.net Entity Framework, and recently published in the market called "field-driven design and model combat", It also highlights a lot of nhibernate in the field of driving the role of design and status, is also the first book related to NHibernate. However, as far as nhibernate is concerned, there is no official document introduced in detail Oh, the garden Kiler has translated him into the Chinese version of the income of a large group is only CET-4. Whether you use NHibernate or LINQ to SQL, use Profiler a trace, execute are SQL statements, so the SQL is the root. Especially for those data-centric applications that implement complex stored procedures in a database, complex report queries, or direct SQL. Of course for those based on. NET in the middle-tier application, they realize the object-oriented business model and the application of business logic, NHibernate is the most useful. In any case, nhibernate can help you eliminate or package the SQL code for a particular vendor, and help you convert the result set from a tabular representation to a series of objects (official documents).

A bit off the topic, no longer long-winded----directly out of the pressure axis problem.

The first question of the finale question

1. Convert table one to Table II

Table I:

Table II:

The database code is as follows:

Code
Code highlighting produced by Actipro Codehighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1 DROP Table #student
2 CREATE TABLE #student (stdname nvarchar (), stdsubject nvarchar (), result int)
3 INSERT into #student VALUES (' John ', ' language ', 80)
4 INSERT into #student values (' John ', ' Math ', 90)
5 INSERT into #student VALUES (' John ', ' Physics ', 85)
6 INSERT into #student VALUES (' Dick ', ' language ', 85)
7 INSERT into #student values (' Dick ', ' math ', 92)
8 INSERT into #student VALUES (' Dick ', ' physics ', 82)
9 INSERT into #student VALUES (' Dick ', ' chemistry ', 82)
INSERT into #student VALUES (' Dick ', ' chemistry ', 82)
SELECT * from #student

Perhaps a lot of veteran, as soon as see this question has the answer. Of course, it's not my intention to post the answer, I'm going to refactor the answer with the new SQL novice. Using Li Jianzhong's favorite words is------I do not recommend a model to apply, but should be from refactoring to mode.

First, you'll think of two groups

1 Select stdname,,from #student GROUP by Stdname

And then. What should be written in the middle.

Code highlighting produced by Actipro Codehighlighter (freeware)
http://www.CodeHighlighter.com/

--> Code
Code highlighting produced by Actipro Codehighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1 case stdsubject when ' chemical ' then result end
2 case Stdsubject when ' language ' then result end
3 case Stdsubject when ' ... ' Then result end
4 case Stdsubject when ' ... ' Then result end
5 case Stdsubject when ' ... ' Then result end

Table Two where the 0 came from.

Code
Code highlighting produced by Actipro Codehighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1 IsNull (sum (case stdsubject when ' chemical ' then result end), 0
2 IsNull (sum (case stdsubject when ' language ' then result), 0)
3 IsNull (sum (case stdsubject when ' ... ' Then result end ', 0)
4 IsNull (sum (case stdsubject when ' ... ' Then result end ', 0)
5 IsNull (SUM (case stdsubject when ' ... ' Then result end ', 0)

So come to the following:

Code
Code highlighting produced by Actipro Codehighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1 Select Stdname,
2 IsNull (sum (case stdsubject when ' chemical ' then result end), 0) [chemistry],
3 IsNull (sum (case stdsubject when ' math ' then result end), 0) [mathematics],
4 IsNull (sum (case stdsubject when ' physical ' then result end), 0) [physical],
5 IsNull (SUM (case stdsubject when ' language ' then result end), 0) [language]
6 from #student
7 GROUP BY Stdname

And then come to the answer:

Code
Code highlighting produced by Actipro Codehighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1 DECLARE @sql varchar (4000)
2 Set @sql = ' Select Stdname '
3 Select @sql = @sql + ', isnull (sum (case stdsubject when ' + Stdsubject + ' "then result), 0) [' + STDSUBJ ECT + '] '
4 from (select distinct stdsubject from #student) as a
5 Select @sql = @sql + ' from #student Group by Stdname '
6 Print @sql
7 EXEC (@sql)
8

The second question of the finale: Turn table two into table one

Table I:

Table II:

The database code is as follows:

Code
Code highlighting produced by Actipro Codehighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1 DROP Table #student2
2 CREATE TABLE #student2 (stdname nvarchar (10), chemical int, math int, physical int, language int)
3 INSERT into #student2 VALUES (' Dick ', 164, 92, 82, 85)
4 INSERT into #student2 VALUES (' John ', 0, 90, 85, 80)
5 SELECT * from #student2

See this problem, think directly:

Code
Highlighting produced by Actipro Codehighlighter (freeware)
http://www. codehighlighter.com/

-->  1  SELECT ' dick ' as  stdname,stdname = ' chemical ',  chemical   as &nbs p;result from  #student2   WHERE  stdname = ' dick '
 2  union all
 3  SELECT ' Dick ' as  stdname,stdname = ' math ',  math   as  result from  #student2   where   ; stdname = ' Dick '
 4  union all
 5  SELECT ' dick ' as  stdname,stdname = ' Physics ', &NB SP; physical   as