在ACCESS中,有个现成的表转置过程:TRANSFORM
但是在SQL Server2000中就没有了,要想实现表的转置,就得自己动手写一个过程了。
要实现这样的功能:
Table Name :QTRSALES
内容:
如果再复杂点的表,可能就比较麻烦了。就算是用下面的方法,也不是很方便:
我最后又找了一下,发现http://www.sqlteam.com/item.asp?ItemID=2955,里面用一个存储过程,把这个功能封装起来了,虽然失去了一部分灵活性,有时候生成的表的列名看起来比较含糊。全部语句如下:
1. Execute crosstab就是执行corsstab这个存储过程,这个应该不用说了吧!
2.Select 语句部分:可以是任何SQL语句,在语法允许的长度范围内,并且要有Group By 项。可以使用表的连接(join),如果要使用表别名(alias),则必须要包含在后面summary项里.
3.summary 项:这里必须包含一个统计函数,SUM(), AVG(), MIN(), MAX(),Count()等都可以。如果要选出唯一列,你要自己再编辑一下源代码,在中间过程中使用DISTINCT.注意,COUNT(*)是不行的,你必须COUNT一个特定的列。
4.PIVOT 列:这个列,就是你要显示在最终表上的列名内的值,你也可以使用一些表达式(a+b,LEFT(FirstName,3),等等)。这些只要在你所提供的表中能找到就可以。最后生成的表会创建每个不同的值的列及其对应的内容。
5.Table:就是所要操作的数据来源表的名字。
开头所列的问题,我用这个存储过程试过了,可以实现。
但是在SQL Server2000中就没有了,要想实现表的转置,就得自己动手写一个过程了。
要实现这样的功能:
Table Name :QTRSALES
内容:
Year Quarter Amount需要的转置效果:
-------------------------------
1995 1 125,000.90
1995 2 136,000.75
1995 3 212,000.34
1995 4 328,000.82
1996 3 728,000.35
1996 2 422,000.13
1996 1 328,000.82
YEAR Q1 Q2 Q3 Q4如果要简单的实现这个表的转置,大家都有很好的方法:
-------------------------------------------------------------------
1995 125,000.90 136,000.75 212,000.34 328,000.82
1996 328,000.82 422,000.13 728,000.35 0.00
SELECT YEAR,这样就可以了。
Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =
Q.YEAR),0),
Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =
Q.YEAR),0),
Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =
Q.YEAR),0),
Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =
Q.YEAR),0)
FROM QTRSALES Q
GROUP BY YEAR
如果再复杂点的表,可能就比较麻烦了。就算是用下面的方法,也不是很方便:
Select year=q.year,因为所有的列,你都要自己加进去,不能够通用起来。
SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1,
SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2,
SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3,
SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4
FROM qtrsales q
GROUP BY year
我最后又找了一下,发现http://www.sqlteam.com/item.asp?ItemID=2955,里面用一个存储过程,把这个功能封装起来了,虽然失去了一部分灵活性,有时候生成的表的列名看起来比较含糊。全部语句如下:
CREATE PROCEDURE crosstab这个存储过程的执行方式如下:
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
SET ANSI_WARNINGS ON
1 | 2 | 3 | 4 | |
EXECUTE crosstab | SELECT statement | summary calculation | pivot column | table name |
1. Execute crosstab就是执行corsstab这个存储过程,这个应该不用说了吧!
2.Select 语句部分:可以是任何SQL语句,在语法允许的长度范围内,并且要有Group By 项。可以使用表的连接(join),如果要使用表别名(alias),则必须要包含在后面summary项里.
3.summary 项:这里必须包含一个统计函数,SUM(), AVG(), MIN(), MAX(),Count()等都可以。如果要选出唯一列,你要自己再编辑一下源代码,在中间过程中使用DISTINCT.注意,COUNT(*)是不行的,你必须COUNT一个特定的列。
4.PIVOT 列:这个列,就是你要显示在最终表上的列名内的值,你也可以使用一些表达式(a+b,LEFT(FirstName,3),等等)。这些只要在你所提供的表中能找到就可以。最后生成的表会创建每个不同的值的列及其对应的内容。
5.Table:就是所要操作的数据来源表的名字。
开头所列的问题,我用这个存储过程试过了,可以实现。