按月份 分列 对同类科目汇总统计数据。请高人指点！

[cheng525jj]

最后统计的结果要求如下:
scode sname 201401 201402 201403 201404 ...........
500101 直接材料 100.00 10000.00
500102 直接人工 7436.00 20000.00
500103 制造费用 1000.00 30000.00
510101 劳保费 501.00 40500.00
510102 招待费 11111.00 55500.00


注:月份是变动的。

具体的表(totalprice)结构与字段如下:
SCode char(20)
SName char(50)
YSMonth char(20)
Price float

SQL语句如下:
select t1.SCode,t1.SName,t1.YSMonth,t1.price from totalprice t1 where SCode is not null
order by t1.YSMonth,t1.SCode,t1.SName

请高人指点！
最好能按月份变动
或月份固定不变各一份指引。

[还在加载中灬]

--动态
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT SCode,SName'
SELECT @SQL=@SQL+',SUM(CASE WHEN YSMonth='''+YSMonth+''' THEN Price END)['+YSMonth+']'
FROM totalprice GROUP BY YSMonth
SET @SQL=@SQL+',SUM(Price)[合计]'
SET @SQL=@SQL+'FROM totalprice GROUP BY SCode,SName'
PRINT @SQL
EXEC(@SQL)
--静态
SELECT SCode,SName
    ,SUM(CASE WHEN YSMonth='201401' THEN Price END)[201401]
    ,SUM(CASE WHEN YSMonth='201402' THEN Price END)[201402]
    ,SUM(CASE WHEN YSMonth='201403' THEN Price END)[201403]
    ,SUM(Price)[合计]
FROM totalprice
GROUP BY SCode,SName

[cheng525jj]

---动态SQL
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT SCode,SName'
SELECT @SQL=@SQL+',SUM(CASE WHEN YSMonth='''+YSMonth+''' THEN Price END)['+YSMonth+']'
FROM totalprice GROUP BY YSMonth
SET @SQL=@SQL+',SUM(Price)[合计]'
SET @SQL=@SQL+'FROM totalprice GROUP BY YSMonth, SCode,SName'
PRINT @SQL
EXEC(@SQL)

报错:
消息 8114，级别 16，状态 5，第 3 行
从数据类型 varchar 转换为 float 时出错。
报错行：SELECT @SQL=@SQL+',SUM(CASE WHEN YSMonth='''+YSMonth+''' THEN Price END)['+YSMonth+']'


静态SQL: 可行运行正常，但结果分类有一些偏差，
要求SCode,SName同类只能显示一行。
具体如图。
SELECT SCode,SName
,SUM(CASE WHEN YSMonth='201401' THEN Price END)[201401]
,SUM(CASE WHEN YSMonth='201402' THEN Price END)[201402]
,SUM(CASE WHEN YSMonth='201403' THEN Price END)[201403]
,SUM(Price)[合计]
FROM totalprice
GROUP BY YsMonth,SCode,SName

[还在加载中灬]

SELECT SCode,SName ,SUM(CASE WHEN YSMonth='201401' THEN Price END)[201401] ,SUM(CASE WHEN YSMonth='201402' THEN Price END)[201402] ,SUM(CASE WHEN YSMonth='201403' THEN Price END)[201403] ,SUM(Price)[合计] FROM totalprice GROUP BY YsMonth,SCode,SName 你自己要加上这个，关我什么事

[还在加载中灬]

--动态
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT SCode,SName'
SELECT @SQL=@SQL+',SUM(CASE WHEN YSMonth='''+CAST(YSMonth AS VARCHAR)+''' THEN Price END)['+YSMonth+']'
FROM totalprice GROUP BY YSMonth
SET @SQL=@SQL+',SUM(Price)[合计]'
SET @SQL=@SQL+'FROM totalprice GROUP BY SCode,SName'
PRINT @SQL
EXEC(@SQL)

[还在加载中灬]

动态再调整一下

--动态
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT SCode,SName'
SELECT @SQL=@SQL+',SUM(CASE WHEN YSMonth='''+CAST(YSMonth AS VARCHAR)+''' THEN Price END)['+CAST(YSMonth AS VARCHAR)+']'
FROM totalprice GROUP BY YSMonth
SET @SQL=@SQL+',SUM(Price)[合计]'
SET @SQL=@SQL+'FROM totalprice GROUP BY SCode,SName'
PRINT @SQL
EXEC(@SQL)

[cheng525jj]

对于动态SQL，我改成带时间参数的存储过程。
create procedure P_Query201404
@StartDate char(20),
@EndDate char(20)
as
begin
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT SCode,SName'
SELECT @SQL=@SQL+',SUM(CASE WHEN YSMonth='''+CAST(YSMonth AS VARCHAR)+''' THEN [Price] END)['+CAST(YSMonth AS VARCHAR)+']'
FROM totalprice where YSMonth between ''+@StartDate+'' and ''+@EndDate+'' GROUP BY YSMonth
SET @SQL=@SQL+',SUM([Price])[合计]'
SET @SQL=@SQL+'FROM [totalprice] where SCode is not null GROUP BY SCode,SName'
PRINT @SQL
EXEC(@SQL)
end

执行存储过程:
exec P_Query201404 '201401','201412'

得到结果月份未按顺序排列。


理想结果:
scode name 201401 201402 201403 ...... 合计

[还在加载中灬]

CREATE PROCEDURE P_Query201404
	@StartDate char(20),
	@EndDate char(20)
AS
BEGIN
	DECLARE @SQL NVARCHAR(MAX)
	SET @SQL='SELECT SCode,SName'
	SELECT @SQL=@SQL+',SUM(CASE WHEN YSMonth='''+YSMonth+'''THEN price END)['+YSMonth+']'
	FROM(
		SELECT CONVERT(VARCHAR(6),@StartDate+number)YSMonth
		FROM master..spt_values
		WHERE type='P' AND number<=(@EndDate+0)-@StartDate
	)T
	SET @SQL=@SQL+',SUM([Price])[合计]'
	SET @SQL=@SQL+'FROM[totalprice]
	Where SCode is not null AND YSMonth BETWEEN'''+@StartDate+'''AND '''+@EndDate+'''
	GROUP BY SCode,SName'
	EXEC(@SQL)
END

[cheng525jj]

用最后一个动态转换存储过程，出现不规则的月份，(如,201415). 执行结果如下:

[还在加载中灬]

CREATE PROCEDURE P_Query201404
	@StartDate char(20),
	@EndDate char(20)
AS
BEGIN
	DECLARE @SQL NVARCHAR(MAX)
	SET @SQL='SELECT SCode,SName'
	SELECT @SQL=@SQL+',SUM(CASE WHEN YSMonth='''+YSMonth+'''THEN price END)['+YSMonth+']'
	FROM(
		SELECT CONVERT(VARCHAR(6),DATEADD(MONTH,number,RTRIM(@StartDate)+'01'),112)YSMonth
		FROM master..spt_values
		WHERE type='P' AND number<=DATEDIFF(MONTH,RTRIM(@StartDate)+'01',RTRIM(@EndDate)+'01')
	)T
	SET @SQL=@SQL+',SUM([Price])[合计]'
	SET @SQL=@SQL+'FROM[totalprice]
	Where SCode is not null AND YSMonth BETWEEN'''+@StartDate+'''AND '''+@EndDate+'''
	GROUP BY SCode,SName'
	EXEC(@SQL)
END

已处理

[cheng525jj]

最好，能增加一列到最后。 最后统计的结果要求如下: scode sname 201401 201402 201403 201404 ........... 合计 500101 直接材料 100.00 10000.00 10100.00 500102 直接人工 7436.00 20000.00 500103 制造费用 1000.00 30000.00 510101 劳保费 501.00 40500.00 510102 招待费 11111.00 55500.00

[还在加载中灬]

--动态
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT SCode,SName'
SELECT @SQL=@SQL+',SUM(CASE WHEN YSMonth='''+YSMonth+''' THEN Price END)['+YSMonth+']'
FROM totalprice GROUP BY YSMonth
SET @SQL=@SQL+'FROM totalprice GROUP BY SCode,SName'
PRINT @SQL
EXEC(@SQL)
--静态
SELECT SCode,SName
    ,SUM(CASE WHEN YSMonth='201401' THEN Price END)[201401]
    ,SUM(CASE WHEN YSMonth='201402' THEN Price END)[201402]
    ,SUM(CASE WHEN YSMonth='201403' THEN Price END)[201403]
FROM totalprice
GROUP BY SCode,SName

[cheng525jj]

如果改成带一个参数年份。 exec P_Query201404 '2014' ------------------------------------------------------------------------------------------------------------- 最终要示的结果： scode Sname 01 02 03 04 05 06 07 08 09 10 11 12 合计 请ky_min支持一下。

[还在加载中灬]

IF OBJECT_ID('P_Query201404')IS NOT NULL
	DROP PROCEDURE P_Query201404
GO
CREATE PROCEDURE P_Query201404
	@Year VARCHAR(4)
AS
BEGIN
	DECLARE @StartDate VARCHAR(6),@EndDate VARCHAR(6)
	SET @StartDate=@Year+'01'
	SET @EndDate=@Year+'12'
	DECLARE @SQL NVARCHAR(MAX)
	SET @SQL='SELECT SCode,SName'
	SELECT @SQL=@SQL+',SUM(CASE WHEN YSMonth='''+YSMonth+'''THEN price END)['+YSMonth+']'
	FROM(
		SELECT CONVERT(VARCHAR(6),DATEADD(MONTH,number,RTRIM(@StartDate)+'01'),112)YSMonth
		FROM master..spt_values
		WHERE type='P' AND number<=DATEDIFF(MONTH,RTRIM(@StartDate)+'01',RTRIM(@EndDate)+'01')
	)T
	SET @SQL=@SQL+',SUM([Price])[合计]'
	SET @SQL=@SQL+'FROM[totalprice]
	Where SCode is not null AND YSMonth BETWEEN'''+@StartDate+'''AND '''+@EndDate+'''
	GROUP BY SCode,SName'
	EXEC(@SQL)
END