两条数据合并成一条

[呵呵哒呀]

No Name Type Month Sum OpData
1 张三 2 2014-02 30 2014-05
1 张三 3 2014-02 40 2014-05
2 张三 2 2014-02 30 2014-05

datagrid显示
编号 姓名 类型2 类型3 月份 录入时间
1 张三 30 40 2014-02 2014-05
2 张三 30 2014-02 2014-05

[呵呵哒呀]

引用 10 楼 DBA_Huangzj 的回复:

你现在查询出来的结果是怎样的？给个大概的完整点的例子，应该加where条件过滤就可以了

declare @s nvarchar(4000) set @s=''
Select  @s=@s+','+quotename(CAST([FeeType]AS VARCHAR))+'=max(case when [FeeType]='+quotename([FeeType],'''')+
'then isnull(isnull(BasicFee,0)+isnull(ChangeFee,0)+isnull(DiffPriceFee,0),0) else 0 end)'from [V_ReceiptFee] group by [FeeType] 
exec('select [InvoiceID],[InvoiceNo],[RoomName],[CustomerName],[PayType],[OpDate],u.username as OpName,[Month]'+@s+' from [V_ReceiptFee] 
left join JQ_T_User u on u.tr_id=OpName where 1=1  and PayType!=''预交冲帐''
and ProID in(10001) and BuildID=''10005''
and OpDate between ''2014-03-20'' and ''2014-05-05''  
group by  [InvoiceID],[InvoiceNo],[RoomName],[CustomerName],[PayType],[OpDate],u.username,[OpName],[Month]
order by InvoiceID  desc
') 

[發糞塗牆]

额。。。出来的结果呢？

[呵呵哒呀]

引用 10 楼 DBA_Huangzj 的回复:

你现在查询出来的结果是怎样的？给个大概的完整点的例子，应该加where条件过滤就可以了

[發糞塗牆]

你试试

declare @s nvarchar(4000) set @s=''
Select  @s=@s+','+quotename(CAST([FeeType]AS VARCHAR))+'=max(case when [FeeType]='+quotename([FeeType],'''')+
'then isnull(isnull(BasicFee,0)+isnull(ChangeFee,0)+isnull(DiffPriceFee,0),0) else 0 end)'from [V_ReceiptFee] WHERE isnull(isnull(BasicFee,0)+isnull(ChangeFee,0)+isnull(DiffPriceFee,0),0)<>0 group by [FeeType] 
exec('select [InvoiceID],[InvoiceNo],[RoomName],[CustomerName],[PayType],[OpDate],u.username as OpName,[Month]'+@s+' from [V_ReceiptFee] 
left join JQ_T_User u on u.tr_id=OpName where 1=1  and PayType!=''预交冲帐''
and ProID in(10001) and BuildID=''10005''
and OpDate between ''2014-03-20'' and ''2014-05-05''  
group by  [InvoiceID],[InvoiceNo],[RoomName],[CustomerName],[PayType],[OpDate],u.username,[OpName],[Month]
order by InvoiceID  desc
')

[呵呵哒呀]

引用 14 楼 DBA_Huangzj 的回复:

你试试

declare @s nvarchar(4000) set @s=''
Select  @s=@s+','+quotename(CAST([FeeType]AS VARCHAR))+'=max(case when [FeeType]='+quotename([FeeType],'''')+
'then isnull(isnull(BasicFee,0)+isnull(ChangeFee,0)+isnull(DiffPriceFee,0),0) else 0 end)'from [V_ReceiptFee] WHERE isnull(isnull(BasicFee,0)+isnull(ChangeFee,0)+isnull(DiffPriceFee,0),0)<>0 group by [FeeType] 
exec('select [InvoiceID],[InvoiceNo],[RoomName],[CustomerName],[PayType],[OpDate],u.username as OpName,[Month]'+@s+' from [V_ReceiptFee] 
left join JQ_T_User u on u.tr_id=OpName where 1=1  and PayType!=''预交冲帐''
and ProID in(10001) and BuildID=''10005''
and OpDate between ''2014-03-20'' and ''2014-05-05''  
group by  [InvoiceID],[InvoiceNo],[RoomName],[CustomerName],[PayType],[OpDate],u.username,[OpName],[Month]
order by InvoiceID  desc
')

额 没有效果 还是有没有数据的列

[呵呵哒呀]

引用 14 楼 DBA_Huangzj 的回复:

你试试

额 有效果 消失了2列 F044 F060

[發糞塗牆]

再试试

declare @s nvarchar(4000) set @s=''
Select  @s=@s+','+quotename(CAST([FeeType]AS VARCHAR))+'=max(case when [FeeType]='+quotename([FeeType],'''')+
'then isnull(isnull(BasicFee,0)+isnull(ChangeFee,0)+isnull(DiffPriceFee,0),0) else 0 end)'from [V_ReceiptFee] WHERE BasicFee IS NOT NULL OR ChangeFee IS NOT NULL OR DiffPriceFee IS NOT NULL  group by [FeeType] 
exec('select [InvoiceID],[InvoiceNo],[RoomName],[CustomerName],[PayType],[OpDate],u.username as OpName,[Month]'+@s+' from [V_ReceiptFee] 
left join JQ_T_User u on u.tr_id=OpName where 1=1  and PayType!=''预交冲帐''
and ProID in(10001) and BuildID=''10005''
and OpDate between ''2014-03-20'' and ''2014-05-05''  
group by  [InvoiceID],[InvoiceNo],[RoomName],[CustomerName],[PayType],[OpDate],u.username,[OpName],[Month]
order by InvoiceID  desc
')

[發糞塗牆]

那就是满足你要求没？

[呵呵哒呀]

引用 17 楼 DBA_Huangzj 的回复:

再试试

declare @s nvarchar(4000) set @s=''
Select  @s=@s+','+quotename(CAST([FeeType]AS VARCHAR))+'=max(case when [FeeType]='+quotename([FeeType],'''')+
'then isnull(isnull(BasicFee,0)+isnull(ChangeFee,0)+isnull(DiffPriceFee,0),0) else 0 end)'from [V_ReceiptFee] WHERE BasicFee IS NOT NULL OR ChangeFee IS NOT NULL OR DiffPriceFee IS NOT NULL  group by [FeeType] 
exec('select [InvoiceID],[InvoiceNo],[RoomName],[CustomerName],[PayType],[OpDate],u.username as OpName,[Month]'+@s+' from [V_ReceiptFee] 
left join JQ_T_User u on u.tr_id=OpName where 1=1  and PayType!=''预交冲帐''
and ProID in(10001) and BuildID=''10005''
and OpDate between ''2014-03-20'' and ''2014-05-05''  
group by  [InvoiceID],[InvoiceNo],[RoomName],[CustomerName],[PayType],[OpDate],u.username,[OpName],[Month]
order by InvoiceID  desc
')

额 这次一行都没有消失

[發糞塗牆]

那就用前面那个吧

[呵呵哒呀]

引用 20 楼 DBA_Huangzj 的回复:

那就用前面那个吧

我又加了三个条件 可以了 现在我在后台怎么 循环取值？ 而不是用 string Sum =ds[F001] string Sum1 =ds[F002] 这种方式取值？

[發糞塗牆]

C#不是很擅长

[呵呵哒呀]

引用 22 楼 DBA_Huangzj 的回复:

C#不是很擅长

囧 好吧 非常感谢大神指导

[ahhfql]

declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename(N'类型'+CAST([type]AS VARCHAR))+'=max(case when [type]='+quotename([type],'''')+' then [Sum] else 0 end)' from [huang] group by [type] exec('select [No],[Name],[Month],[OpData]'+@s+' from [huang] group by [No],[Name],[Month],[OpData]') 弱弱的问一下 '=max(case when [type]='+quotename([type],'''')+ 中四个''''什么意思

[發糞塗牆]

会有不固定的类型数量不？比如类型4、5、6、。。。。。，另外类型1去哪了？

[發糞塗牆]

列名你自己改改吧

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-05-06 10:53:18
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
--	Apr  2 2010 15:48:46 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([No] int,[Name] nvarchar(4),[Type] int,[Month] nvarchar(14),[Sum] int,[OpData] nvarchar(14))
insert [huang]
select 1,N'张三',2,'2014-02',30,'2014-05' union all
select 1,N'张三',3,'2014-02',40,'2014-05' union all
select 2,N'张三',2,'2014-02',30,'2014-05'
--------------生成数据--------------------------
declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename(N'类型'+CAST([type]AS VARCHAR))+'=max(case when [type]='+quotename([type],'''')+' then [Sum] else 0 end)'
from [huang] group by [type]
exec('select [No],[Name],[Month],[OpData]'+@s+' from [huang] group by [No],[Name],[Month],[OpData]')
----------------结果----------------------------
/* 
No          Name Month          OpData         类型2         类型3
----------- ---- -------------- -------------- ----------- -----------
1           张三   2014-02        2014-05        30          40
2           张三   2014-02        2014-05        30          0
*/

[呵呵哒呀]

引用 1 楼 DBA_Huangzj 的回复:

会有不固定的类型数量不？比如类型4、5、6、。。。。。，另外类型1去哪了？

额 会有 这个不固定

[發糞塗牆]

我已经写了动态的了

[呵呵哒呀]

引用 4 楼 DBA_Huangzj 的回复:

我已经写了动态的了

我的Sum 是这么得到的 要怎么放进去？ isnull(isnull(BasicFee,0)+isnull(ChangeFee,0)+isnull(DiffPriceFee,0),0) as Sum

[發糞塗牆]

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename(N'类型'+CAST([type]AS VARCHAR))+'=max(case when [type]='+quotename([type],'''')+' then isnull(isnull(BasicFee,0)+isnull(ChangeFee,0)+isnull(DiffPriceFee,0),0) else 0 end)'
from [huang] group by [type]
exec('select [No],[Name],[Month],[OpData]'+@s+' from [huang] group by [No],[Name],[Month],[OpData]')

试试，如果不行的话，用个临时表先把sum算出来，也就是得出你一开始的结果集然后再运算