;with f as
(
select id=row_number()over(partition by 字段1, 字段2 order by getdate())),* from tb
)select
字段1, 字段2,
max(case when id=1 then 字段3 else '' end) as 字段3,
max(case when id=2 then 字段3 else '' end) as 字段4,
max(case when id=3 then 字段3 else '' end) as 字段5
from
f
group by
字段1, 字段2
(
select id=row_number()over(partition by 字段1, 字段2 order by getdate())),* from tb
)select
字段1, 字段2,
max(case when id=1 then 字段3 else '' end) as 字段3,
max(case when id=2 then 字段3 else '' end) as 字段4,
max(case when id=3 then 字段3 else '' end) as 字段5
from
f
group by
字段1, 字段2
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-06-17 10:27:19
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([字段1] int,[字段2] int,[字段3] varchar(2),[序号] int)
insert [tb]
select 1,1,'n1',1 union all
select 1,1,'n2',2 union all
select 1,1,'n3',3 union all
select 2,2,'s1',4 union all
select 2,2,'s2',5 union all
select 2,2,'s3',6
--------------开始查询--------------------------
;with f as
(
select id=row_number()over(partition by 字段1, 字段2 order by getdate()),* from tb
)select
字段1, 字段2,
max(case when id=1 then 字段3 else '' end) as 字段3,
max(case when id=2 then 字段3 else '' end) as 字段4,
max(case when id=3 then 字段3 else '' end) as 字段5
from
f
group by
字段1, 字段2
----------------结果----------------------------
/* 字段1 字段2 字段3 字段4 字段5
----------- ----------- ---- ---- ----
1 1 n1 n2 n3
2 2 s1 s2 s3(2 行受影响)
*/动态的交给楼下。
字段3 联在一起*/SELECT
字段1
, 字段2
, ( SELECT 字段3+ ';' FROM TABLE AS B
WHERE B.字段1 = A.字段1 AND B.字段2 = A.字段2 ORDER BY B.序号 FOR XML PATH( '' ) )
FROM TABLE AS A
GROUP BY A.字段1, A.字段2