简单的数据统计！！100分(100分)

我今天面试叫做一提没做出来，现在叫高手帮帮忙，有三个表<br>A）clientID &nbsp;goldID amount &nbsp;B) goldID goldName &nbsp; C) clientID clientName<br>&nbsp; 001 &nbsp; &nbsp; &nbsp; &nbsp;A &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; 18K &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 001 &nbsp; &nbsp; &nbsp;张三<br>&nbsp; 002 &nbsp; &nbsp; &nbsp; &nbsp;B &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; B &nbsp; &nbsp; 14K &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002 &nbsp; &nbsp; &nbsp;李四<br>&nbsp; 003 &nbsp; &nbsp; &nbsp; &nbsp;C &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; C &nbsp; &nbsp; 95T0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;003 &nbsp; &nbsp; &nbsp;王五<br>现在要写出统计出下表的的SQL语句：<br>clientName &nbsp;18k &nbsp; 14k &nbsp; 95T0<br>张三 &nbsp; &nbsp; &nbsp; &nbsp; 4 &nbsp; &nbsp; 0 &nbsp; &nbsp; 0<br>李四 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; 2 &nbsp; &nbsp; 0<br>王五 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; 0 &nbsp; &nbsp; 2<br>请各位高手指点下，谢谢了！急！

要求一条语句到位?<br><br><br><br>典型的行转列

select cc.clientName,'18k'=case when aa.Goldid='a' then aa.Amount else 0 end, <br>'14k'=case when aa.Goldid='b' then aa.Amount else 0 end, <br>' 95T0'=case when aa.Goldid='c' then aa.Amount else 0 end <br>from aa inner join cc<br>on aa.clientid=cc.clientid<br>inner join bb<br>on aa.goldid=bb.goldid

declare @sql varchar(8000)<br>set @sql = 'C.clientName,'<br>select @sql = @sql + 'sum(case B.goldName <br>when '''+B.goldName +''' <br>&nbsp;then A.amount else 0 end) as '''+B.goldName+''','<br>&nbsp; from (select distinct B.goldName from B) as a<br>--print @sql<br>select @sql = left(@sql,len(@sql)-1) + ' from A inner join C on A.clientid=C.clientid inner join B on A.goldid=B.goldid &nbsp;<br>'<br>print @sql<br>exec(@sql)<br>go

典型的列转行

if object_id('tempdb.dbo.#A') is not null &nbsp;Drop table #A<br>create Table #A<br>(<br>clientID int ,<br>goldID varchar(10),<br>amount varchar(20)<br>)<br>Insert into #A<br>Select '001','A','4' union all<br>Select '002','B','2' union all<br>Select '003','C','2'<br><br>if object_id('tempdb.dbo.#B') is not null &nbsp;Drop table #B<br>create Table #B<br>(<br>goldID varchar(10),<br>goldName varchar(20)<br>)<br>Insert into #B<br>Select 'A','18K' union all<br>Select 'B','14K' union all<br>Select 'C','95T0'<br><br><br>if object_id('tempdb.dbo.#C') is not null &nbsp;Drop table #C<br>create Table #C<br>(<br>clientID int,<br>clientName varchar(20)<br>)<br>Insert into #C<br>Select '001','张三' union all<br>Select '002','李四' union all<br>Select '003','王五'<br><br><br>Select #C.clientName,<br>case #B.goldName when '18K' then #A.amount else 0 end as '18K',<br>case #B.goldName when '14K' then #A.amount else 0 end as '14K',<br>case #B.goldName when '95T0' then #A.amount else 0 end as '95T0' <br>&nbsp;From #A<br>left join #B on #A.goldID=#B.goldID<br>left join #C on #A.clientID=#C.clientID<br>Group by #A.clientID,#C.clientName,#B.goldName,#A.amount<br>Order By #A.clientID

select a.clientName,<br>case when a.goldName = '18K' then a.amount end '18K',<br>case when a.goldName = '14K' then a.amount end '14K',<br>case when a.goldName = '95T0' then &nbsp;a.amount end '95T0'<br>from<br>(select c.clientName,b.goldName,a.amount from #A a<br>left join #B b on a.goldID = b.goldID<br>left join #C c on a.clientID = c.clientID ) a

同意eloveme的