34,838
社区成员




IF OBJECT_ID ('tb') IS NOT NULL DROP table tb
go
CREATE table tb(訂單號 varchar(20),發票號 varchar(10),金額1 int,金額2 int, 其它 varchar(5))
insert tb select
'BK090427008', '96613', 4, 3, 'A' union all select
'BK090427008', '96614', 4, 4, 'A' union all select
'BK090427008', '96615', 4, 5, 'A'
declare @s varchar(200)
select @s=isnull(@s+',','')+ 發票號 from tb
select @s=訂單號+','+@s+','+ltrim(sum(金額1))+','+ltrim(sum(金額2))+','+其它 from tb group by 訂單號,其它
select @s
----------------
BK090427008,96613,96614,96615,12,12,A
(1 行受影响)
CREATE TABLE #t
(
id VARCHAR(8),
fp VARCHAR(8),
price1 INT,
price2 INT,
qt VARCHAR(8)
)
GO
INSERT INTO #t
SELECT '01','1',1,1,'A' UNION ALL
SELECT '01','2',2,2,'A' UNION ALL
SELECT '01','3',3,3,'A' UNION ALL
SELECT '01','4',4,4,'A'
GO
SELECT ID,发票1=MAX(CASE WHEN FP='1' THEN FP ELSE 0 END),发票2=MAX(CASE WHEN FP='2' THEN FP ELSE 0 END),发票3=MAX(CASE WHEN FP='3' THEN FP ELSE 0 END),发票4=MAX(CASE WHEN FP='4' THEN FP ELSE 0 END),SUM(price1) AS 金额1,SUM(price2)AS 金额2,qt FROM #t GROUP BY ID,qt
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-14 16:31:15
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([訂單號] varchar(11),[發票號] int,[金額1] int,[金額2] int,[其它] varchar(1))
insert [tb]
select 'BK090427008',96613,4,3,'A' union all
select 'BK090427008',96614,4,4,'A' union all
select 'BK090427008',96615,4,5,'A'
--------------开始查询--------------------------
select
[訂單號],
[發票號]=stuff((select ','+ltrim([發票號]) from tb t where [訂單號]=tb.[訂單號] for xml path('')), 1, 1, ''),
sum([金額1]) as [金額1],
sum([金額2]) as [金額2],[其它]
from
[tb]
group by
[訂單號],[其它]
----------------结果----------------------------
/* 訂單號 發票號 金額1 金額2 其它
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----
BK090427008 96613,96614,96615 12 12 A
(1 行受影响)
*/