Application and Implementation of SQL cross tabulation

Application and Implementation of SQL cross tabulation · programming learning · Network of Programming Technology Information

Static statement:Question: How to put the following form Fitemid fname fstockid fdeptid fauxqty ------------------- 337 Production Department 598 327 1000.0 337 packaging workshop 624 605 1000.0 339 packaging workshop 598 605 200.0 .. .. .. Convert to this form Fitemid: fstockid, packaging workshop, Production Department ------------------- 337 1000. 598 337 0 1000. 624 339 0 200. 598 We can see from the above that the result is to implement a cross table. To solve the above problem, the simplest method is to use the case function. Let's take a look at the meaning of the Case function: The Case function is a special Transact-SQL expression that allows explicit optional values by column values. Changes in the data are temporary, and no permanent changes are made to the data. For example, the case function can display California in the query result set of rows with Ca values in the State column. The case function includes: Case keyword. Name of the column to be converted. Specify the when clause of the expression to be searched and the then clause that specifies the expression to replace. End keyword. Optional as clause that defines the alias of the Case function. From this, we come up with the solution to the above problem: Select fitemid (Case fname when 'production Department 'then fauxqty else 0 end) as 'production production ', (Case fname when 'Packaging workshop 'then fauxqty else 0 end) as 'Packaging workshop ', Fstockid, fdeptid From testtable The above is just a simple example. As long as we understand the correct usage of the Case function, it is easy to write complex cross tables.Dynamic SQL statementsIn the previous article SQL cross table implementation http://www.cnblogs.com/bonny.wong/archive/2005/01/22/95911.html, I gave a simple example of static Implementation of SQL cross table, the results were criticized by a few friends, have no choice but to face, I wrote a new code to dynamically implement the cross tabulation to thank you for your enthusiastic audience. The example is still illustrated by a simple example in the previous article. The solution code is as follows: declare @ sqltext nvarchar (2000) Select @ sqltext = 'select fitemid ,' Select @ sqltext = @ sqltext + '(Case fname when ''' + fname + ''' then fauxqty else 0 end) as ''' + fname + ''', 'From (select distinct fname from test) as Select @ sqltext = left (@ sqltext, Len (@ sqltext)-1) + ', fstockid, fdeptid from Test' Print @ sqltext Exec (@ sqltext) Go Note that the first select statement cannot be combined with the second SELECT statement. If you do not believe it, try it. Why? For the moment, you can also think about it yourself. In the third slect statement, I used the left function to remove a "," and added a "," to the end of the statement because the code can be clearer. The Code has been tested by me.