statement of problem:
How to put the following Fitmid Fstockid Fdeptid Fauxqty -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Transfer to this form of Fitemid production unit packaging workshop fstockid ------------------ 337 0 1000. 624 339 0 200. 598
From above we can see that the result is to implement a crosstab, to solve the above problems, the simplest method is to use the CASE function. Let's take a look at the interpretation of the CASE function:
The CASE function is a special Transact-SQL expression that allows explicit options by column values. Changes in the data are temporary and no permanent changes are permanently changed. 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 contains: Case keyword. A column name that needs to be converted. Specifies the gen clause of the expression to search and specify the THEN clause to replace their expressions. End keyword. Optionally, define the AS clause of the Case function alias.
As a result, we have the solution to the above problems:
SELECT
Fitemid,
Case
FName
WHEN
'
Production department
'
THEN
Fauxqty
Else
0
End
)
AS
'
Production department
'
, (
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 also easy to write a complex crosstab.
Reprint please indicate: cnblogs (Bonny.wong) 2005.1.22
