日期:2014-05-17 浏览次数:20612 次
USE test
GO
-->生成表in_stock
if object_id('in_stock') is not null
drop table in_stock
Go
Create table in_stock([mth] smallint,[indate] datetime,[stock_no] nvarchar(3),[stock_name] nvarchar(5),[qty] smallint)
Insert into in_stock
Select 1,'2012/1/2',N'001',N'TCL电视',5
Union all Select 1,'2012/1/5',N'001',N'TCL电视',6
Union all Select 2,'2012/2/6',N'001',N'TCL电视',10
Union all Select 1,'2012/1/3',N'002',N'格力空调',20
Union all Select 1,'2012/1/4',N'002',N'格力空调',20
Union all Select 2,'2012/2/8',N'002',N'格力空调',30
DECLARE @col NVARCHAR(MAX)
SELECT
@col=ISNULL(@col+',','')+'SUM(CASE WHEN mth='+LTRIM(mth)+' THEN qty END) As ['+LTRIM(mth)+'月份]'
FROM in_stock
GROUP BY mth
EXEC ( N'SELECT [stock_no] As 编码,[stock_name] As 名称,'+@col+' FROM in_stock GROUP BY [stock_no],[stock_name]' )
/*
编码 名称 1月份 2月份
---- ----- ----------- -----------
001 TCL电视 11 10
002 格力空调 40 30
*/