CREATE FUNCTION dbo.f_str(@iname varchar(10))
RETURNS varchar(2000)
AS
BEGIN
DECLARE @re varchar(2000)
SET @re=''
SELECT @re=@re+' '+isnull(备注,'')
FROM TableName
WHERE 费用名称=@iname
RETURN(STUFF(@re,1,1,''))
END
select 费用名称,dbo.f_str(费用名称)as 备注 into #remark from Tablename group by 费用名称
declare @s varchar(8000)
set @s=''
select @s=@s+',['+費用月份+']=sum(case 費用月份 when '''+費用月份+''' then 費用 else null end )' from aaaa group by 費用月份 order by 費用月份
exec ( 'select a.费用名称+@s+',b.备注 from Tablename a,#remark b where a.费用名称=b.费用名称 group by a.费用名称,b.备注)
CREATE FUNCTION dbo.f_str(@iname varchar(10))
RETURNS varchar(2000)
AS
BEGIN
DECLARE @re varchar(2000)
SET @re=''
SELECT @re=@re+' '+isnull(备注,'')
FROM TableName
WHERE 费用名称=@iname
RETURN(STUFF(@re,1,1,''))
END
--查询
select
费用名称,
isnull(sum(case 费用月份 when 1 then 费用 else 0 end),0) as 一月份,
isnull(sum(case 费用月份 when 2 then 费用 else 0 end),0) as 二月份,
isnull(sum(case 费用月份 when 3 then 费用 else 0 end),0) as 三月份,
isnull(sum(case 费用月份 when 4 then 费用 else 0 end),0) as 四月份,
isnull(sum(case 费用月份 when 5 then 费用 else 0 end),0) as 五月份,
isnull(sum(case 费用月份 when 6 then 费用 else 0 end),0) as 六月份,
isnull(sum(case 费用月份 when 7 then 费用 else 0 end),0) as 七月份,
isnull(sum(case 费用月份 when 8 then 费用 else 0 end),0) as 八月份,
isnull(sum(case 费用月份 when 9 then 费用 else 0 end),0) as 九月份,
isnull(sum(case 费用月份 when 10 then 费用 else 0 end),0) as 十月份,
isnull(sum(case 费用月份 when 11 then 费用 else 0 end),0) as 十一月份,
isnull(sum(case 费用月份 when 11 then 费用 else 0 end),0) as 十二月份,
dbo.F_STR(费用名称)
from
tablename
group by
费用名称