我在sql server 2000作了测试 其中,test表的结构定义如下: type int in_date datetime vendor char(30) pcs int 测试数据: type in_date 测试如下: declare @date1 datetime declare @date2 datetime select @date1 = '1974-01-01' select @date2 = '1978-01-02' --获得产品类型(时间在@date1与@date2之间) select type from test where in_date between @date1 and @date2 group by type --获得总的产品数量(时间在@date1与@date2之间) select sum(pcs) from test where in_date between @date1 and @date2 在sql server 2000中申明局部变量的时候要加@,而且在用关键字between时必须是@date1的值小于@date2的值
可参见MSDN:TRANSFORM Statement Creates a crosstab query.SyntaxTRANSFORM aggfunction selectstatement PIVOT pivotfield [IN (value1[, value2[, ...]])]The TRANSFORM statement has these parts:Part Description aggfunction An SQL aggregate function that operates on the selected data. selectstatement A SELECT statement. pivotfield The field or expression you want to use to create column headings in the query's result set. value1, value2 Fixed values used to create column headings. ResWhen you summarize data using a crosstab query, you select values from specified fields or expressions as column headings so you can view data in a more compact format than with a select query.TRANSFORM is optional but when included is the first statement in an SQL string. It precedes a SELECT statement that specifies the fields used as row headings and a GROUP BY clause that specifies row grouping. Optionally, you can include other clauses, such as WHERE, that specify additional selection or sorting criteria. You can also use subqueries as predicates — specifically, those in the WHERE clause — in a crosstab query.The values returned in pivotfield are used as column headings in the query's result set. For example, pivoting the sales figures on the month of the sale in a crosstab query would create 12 columns. You can restrict pivotfield to create headings from fixed values (value1, value2 ) listed in the optional IN clause. You can also include fixed values for which no data exists to create additional columns.
这个是什么意思?
select type ,sum(pcs)
from tab_name
where in_date between date1 and date2
group by type
pivot vendor
但不知若在sql server中实现相同功能该如何实现
其中,test表的结构定义如下:
type int
in_date datetime
vendor char(30)
pcs int
测试数据:
type in_date
测试如下:
declare @date1 datetime
declare @date2 datetime
select @date1 = '1974-01-01'
select @date2 = '1978-01-02'
--获得产品类型(时间在@date1与@date2之间)
select type
from test
where in_date between @date1 and @date2
group by type
--获得总的产品数量(时间在@date1与@date2之间)
select sum(pcs)
from test
where in_date between @date1 and @date2 在sql server 2000中申明局部变量的时候要加@,而且在用关键字between时必须是@date1的值小于@date2的值
pivot vendor表示用vendor不同的值产生列名。
Creates a crosstab query.SyntaxTRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]The TRANSFORM statement has these parts:Part Description
aggfunction An SQL aggregate function that operates on the selected data.
selectstatement A SELECT statement.
pivotfield The field or expression you want to use to create column headings in the query's result set.
value1, value2 Fixed values used to create column headings.
ResWhen you summarize data using a crosstab query, you select values from specified fields or expressions as column headings so you can view data in a more compact format than with a select query.TRANSFORM is optional but when included is the first statement in an SQL string. It precedes a SELECT statement that specifies the fields used as row headings and a GROUP BY clause that specifies row grouping. Optionally, you can include other clauses, such as WHERE, that specify additional selection or sorting criteria. You can also use subqueries as predicates — specifically, those in the WHERE clause — in a crosstab query.The values returned in pivotfield are used as column headings in the query's result set. For example, pivoting the sales figures on the month of the sale in a crosstab query would create 12 columns. You can restrict pivotfield to create headings from fixed values (value1, value2 ) listed in the optional IN clause. You can also include fixed values for which no data exists to create additional columns.