如何将同一时间的多条记录合并为一条记录？

[fleamboy]

如下数据：（同一时间ID的数量是固定的）
ID time D1 D2
---------------------------------------
1 2010-9-9 1.1 100
2 2010-9-9 2.1 200
3 2010-9-9 3.1 300
4 2010-9-9 4.1 400
1 2010-9-10 1.2 100
2 2010-9-10 2.2 200
3 2010-9-10 3.2 300
4 2010-9-10 4.2 400

查询生产如下记录
ID time ID1 D11 D21 ID2 D21 D22 id3 D31 D32 id4 D41 D42
-------------------------------------------------------
1 2010-9-9 1 1.1 100 2 2.1 200 3 3.1 300 4 4.1 400
2 2010-9-10 1 1.2 100 2 2.2 200 3 3.2 300 4 4.2 400

[claro]

case when

[lcw321321]

SQL 2005的行列转换函数可以实现，好久没有用，不太会使了，你自己查查吧，要不等楼下的来

[SQLCenter]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(ID int, time datetime, D1 float, D2 int)
insert into #
select 1, '2010-9-9', 1.1, 100 union all
select 2, '2010-9-9', 2.1, 200 union all
select 3, '2010-9-9', 3.1, 300 union all
select 4, '2010-9-9', 4.1, 400 union all
select 1, '2010-9-10', 1.2, 100 union all
select 2, '2010-9-10', 2.2, 200 union all
select 3, '2010-9-10', 3.2, 300 union all
select 4, '2010-9-10', 4.2, 400

select * from # a, # b, # c, # d where a.time=b.time and a.time=c.time and a.time=d.time
and a.id=1 and b.id=2 and c.id=3 and d.id=4

/*
ID          time                    D1                     D2          ID          time                    D1                     D2          ID          time                    D1                     D2          ID          time                    D1                     D2
----------- ----------------------- ---------------------- ----------- ----------- ----------------------- ---------------------- ----------- ----------- ----------------------- ---------------------- ----------- ----------- ----------------------- ---------------------- -----------
1           2010-09-09 00:00:00.000 1.1                    100         2           2010-09-09 00:00:00.000 2.1                    200         3           2010-09-09 00:00:00.000 3.1                    300         4           2010-09-09 00:00:00.000 4.1                    400
1           2010-09-10 00:00:00.000 1.2                    100         2           2010-09-10 00:00:00.000 2.2                    200         3           2010-09-10 00:00:00.000 3.2                    300         4           2010-09-10 00:00:00.000 4.2                    400
*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-10-20 21:55:39
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID] int,[time] datetime,[D1] numeric(2,1),[D2] int)
insert [tb]
select 1,'2010-9-9',1.1,100 union all
select 2,'2010-9-9',2.1,200 union all
select 3,'2010-9-9',3.1,300 union all
select 4,'2010-9-9',4.1,400 union all
select 1,'2010-9-10',1.2,100 union all
select 2,'2010-9-10',2.2,200 union all
select 3,'2010-9-10',3.2,300 union all
select 4,'2010-9-10',4.2,400
--------------开始查询--------------------------
select * from
(select
  [time],
  max(case  id when 1 then d1 else 0 end) as id1,
  max(case  id when 2 then d1 else 0 end) as id2,
  max(case  id when 3 then d1 else 0 end) as id3,
  max(case  id when 4 then d1 else 0 end) as id4
from
  tb 
group by 
  [time]
)a
left join
(select
  [time],
  max(case  id when 1 then d2 else 0 end) as d11,
  max(case  id when 2 then d2 else 0 end) as d22,
  max(case  id when 3 then d2 else 0 end) as d33,
  max(case  id when 4 then d2 else 0 end) as d44
from
  tb 
group by 
  [time]
)b
 on
a.time=b.time
----------------结果----------------------------
/*time                    id1                                     id2                                     id3                                     id4                                     time                    d11         d22         d33         d44
----------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------------------- ----------- ----------- ----------- -----------
2010-09-09 00:00:00.000 1.1                                     2.1                                     3.1                                     4.1                                     2010-09-09 00:00:00.000 100         200         300         400
2010-09-10 00:00:00.000 1.2                                     2.2                                     3.2                                     4.2                                     2010-09-10 00:00:00.000 100         200         300         400

(2 行受影响) 
*/