"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