小弟有一 Table，內有3個欄位分別是 date, name, money，記錄著每人(大約有10個人，人數變動不大)每日的花費。
在此請教各位大大，可以 select 出如下的 table 嗎??

name	整年份money total	一月份money total	二月份money total
a	100	111	55
b	222	222	66
c	111	33	77
d	222	4	88

在此先謝謝各位大大指教! ^o^

------
請多多指教，謝謝！

<textarea class="sql" rows="10" cols="60" name="code"> --************************* --Mssql demo --1. Data prepare declare @MoneySpent table ( ID varchar(32) , spentdate datetime , SpentMoney Money ) insert into @MoneySpent values('a','2007-01-01',10) insert into @MoneySpent values('a','2007-01-02',15) insert into @MoneySpent values('a','2007-02-01',20) insert into @MoneySpent values('a','2007-02-02',25) insert into @MoneySpent values('b','2007-01-01',30) insert into @MoneySpent values('b','2007-01-02',35) insert into @MoneySpent values('b','2007-02-01',40) insert into @MoneySpent values('b','2007-02-05',45) --2. Month prepare declare @month varchar(6) , @monthFrom varchar(6) , @monthTo varchar(6) select @monthFrom='200701' , @monthTo='200702' declare @MonthTable Table ( MonthStr varchar(6) ) declare @i int select @month=@monthfrom while @month<=@monthTo begin insert into @MonthTable values(@month) select @i = cast( substring(@month,1,4) as int) * 12 cast( substring(@month,5,2) as int) -1 select @i = @i 1 select @Month = cast ( (@i / 12) * 100 (@i % 12) 1 as nvarchar(6) ) end --3. Select prepare select a.id , a.MonthStr , a.TotalM from ( select a.id , a.MonthStr , sum(b.SpentMoney) as TotalM from ( select a.MonthStr , b.ID from @MonthTable a cross join( select id from @MoneySpent group by id ) b ) a left join @MoneySpent b on a.id=b.id and cast(a.MonthStr as int)=(year(b.SpentDate)*100 month(b.SpentDate)) group by a.id , a.MonthStr union select a.id , '***' as MonthStr, sum(SpentMoney) as TotalM from @MoneySpent a where (year(a.SpentDate)*100 month(a.SpentDate)) between cast(@monthFrom as int) and cast(@monthTo as int) group by a.id ) a order by a.id , a.MonthStr</textarea>

<textarea class="sql" rows="10" cols="60" name="code">SELECT T.NAME, SUM(T.MONEY) MONTY, //整年份money total (SELECT SUM(MONEY) FROM TABLE WHERE DATE >= '2007/1/1' AND DATE <= '2007/1/31' AND NAME = T.NAME) MONEY1, //一月份money total (SELECT SUM(MONEY) FROM TABLE WHERE DATE >= '2007/2/1' AND DATE <= '2007/2/28' AND NAME = T.NAME) MONEY2//二月份money total FROM TABLE T WHERE T.DATE >= '2007/1/1' AND T.DATE <= '2007/12/31' GROUP BY T.NAME </textarea>
~小弟淺見，參考看看~

------
~小弟淺見，參考看看~

一个交叉表的问题:
两种方式,可以动态也可以是静态的.
动态的方法:
declare @xcolums nvarchar(4000),@StartMonth char(6) , @EndMonth char(6)
select @xcolums = '' , @StartMonth = '200701' , @EndMonth = '200712'
while @StartMonth < = @EndMonth
begin
set @xcolums = @xcolums '[' @StartMonth '] = sum(case when CONVERT(char(6),[date],112)= ''' @StartMonth ''' then [money] else 0 end) , '
if right(@StartMonth,2) = '12'
set @StartMonth = convert(char(4),convert(int,left(@StartMonth,4)) 1) '01'
else
set @STartMonth = left(@StartMonth,4) right('0' convert(varchar(2),convert(int,right(@StartMonth,2)) 1),2)
end
set @xcolums = 'select name, ' @xcolums ' Total = sum([money]) '
'from [table] group by name order by name '
exec(@xcolums)

静态的比较长,但是简单:
select frmcode,frmname,funit, frmtypname,frmtypcode,
sum(case CONVERT(char(6),[date],112) when '200701' then [Money] else 0 end ) as '200701',
……
……
……
sum(case CONVERT(char(6),[date],112) when '200712' then [Money] else 0 end ) as '200712',
sum(money) as Total
from [table]
group by [name] order by [name]

感謝樓上三位大大傾囊相受，著實讓小弟受益匪淺!
小弟現在使用的語法如下，完全符合小弟的需求。
select s_id,
sum(case CONVERT(char(7),s_date,112) when '2007/01' then s_prod else 0 end ) as '200701',
sum(case CONVERT(char(7),s_date,112) when '2007/02' then s_prod else 0 end ) as '200702',
sum(s_prod) as Total
from hotel2007
group by s_id
order by s_id

另外香奈兒大大所用的語法，小弟會卡在
AND DATE <= '2007/12/31' AND NAME = T.NAME) MONEY, //整年份money total
NAME = T.NAME <-- 這邊會出錯，不知道是哪裡出問題

謝謝各位，問題已結案 但是分數不知道要給哪位大大 >o<
maakite大 目前還沒有積分，所以小弟把分數給他 希望兩位大大別見怪~

------
請多多指教，謝謝！

改成
Table.Name = T.Name

------
先查HELP
再查GOOGLE
最後才發問

沒人有義務替你解答問題
在標題或文章中標明很急
並不會增加網友回答速度

Developing Tool:
1.Delphi 6
2.Visual Studio 2005
3.Visual Studio 2008
DBMS:
MS-SQL