create table [UserTB]
(
[cliID] int IDENTITY (1,1) PRIMARY KEY ,
[Uname] Char(10),
[sex] Char(2),
[Age] tinyint)

create table [FruitTB]
(
[OrderID] int IDENTITY (1,1) PRIMARY KEY ,
[Fname] Char(10),
[cliID] int ,
[amount] int ,
[Smoney] money
)

INSERT INTO [UserTB] (Uname,sex,Age) VALUES ('张三','男',33)
INSERT INTO [UserTB] (Uname,sex,Age) VALUES ('李四','男',28)
INSERT INTO [UserTB] (Uname,sex,Age) VALUES ('王五','男',25)

INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',1,100,200)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',2,250,750)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',2,100,300)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',1,150,450)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',2,100,200)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',2,100,200)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',1,100,300)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',1,150,300)
go

select
    a.Uname 客户名称,
    a.sex 性别,
    a.Age 年龄,
    SUM(case when [Fname]='苹果' then [amount] else 0 end) as 苹果数量,
    SUM(case when [Fname]='苹果' then [Smoney] else 0 end) as 苹果金额,
    SUM(case when [Fname]='雪梨' then [amount] else 0 end) as 雪梨数量,
    SUM(case when [Fname]='雪梨' then [Smoney] else 0 end) as 雪梨金额
from
    [UserTB] a
left join
    [FruitTB] b
on
    a.cliID=b.cliID
group by
    a.Uname,
    a.sex,
    a.Age

/*
客户名称    性别    年龄    苹果数量    苹果金额    雪梨数量    雪梨金额
------------------------------------------------
李四          男    28    200    400.00    350    1050.00
王五          男    25    0    0.00    0    0.00
张三          男    33    250    500.00    250    750.00
*/

------解决方案--------------------

SQL code

create table [UserTB]
(
[cliID] int IDENTITY (1,1) PRIMARY KEY ,
[Uname] Char(10),
[sex] Char(2),
[Age] tinyint)

create table [FruitTB]
(
[OrderID] int IDENTITY (1,1) PRIMARY KEY ,
[Fname] Char(10),
[cliID] int ,
[amount] int ,
[Smoney] money
)

INSERT INTO [UserTB] (Uname,sex,Age) VALUES ('张三','男',33)
INSERT INTO [UserTB] (Uname,sex,Age) VALUES ('李四','男',28)
INSERT INTO [UserTB] (Uname,sex,Age) VALUES ('王五','男',25)

INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',1,100,200)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',2,250,750)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',2,100,300)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',1,150,450)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',2,100,200)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',2,100,200)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',1,100,300)
INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',1,150,300)
go

--动态实现:
declare @str varchar(2000)
set @str=''
select 
    @str=@str+','+rtrim([Fname])+'数量=sum(case when rtrim([Fname])='+
    QUOTENAME(rtrim([Fname]),'''')+' then [amount] else 0 end),'
    +rtrim([Fname])+'金额=sum(case when rtrim([Fname])='+QUOTENAME(rtrim([Fname]),'''')
    +' then [Smoney] else 0 end)'
from
    [FruitTB]
group by
    [Fname]
print @str
set @str='select a.[Uname] as 客户名称,a.[sex] as 性别,a.[Age] as 年龄'
    +@str+' from [UserTB] a left join [FruitTB] b on a.[cliID]=b.[cliID]
     group by a.[Uname],a.[sex],a.[Age]'
exec(@str)


/*
客户名称    性别    年龄    苹果数量    苹果金额    雪梨数量    雪