怎么样将多条记录合成一条记录

  现在有这样一个记录集：
   id   num 
   001  20012
   002  2321
   001  3625
   001  369
   002  363
   ...   ...
要将具有相同id的记录合成一条记录：
   id   num1     num2    num3
   001  20012    3625    369
   002  2321     363      NULL
   ...  ...      ...      ....
应该怎么做？

---

这个问题第1个回答：
行列转换例子参考

SQL code

  
  --建立测试环境

Create Table T(Item varchar(4),Type varchar(4),Qty integer)

--插入数据

insert into T

select 'A','T1','2' union

select 'A','T2','5' union

select 'B','T2','1' union

select 'B','T3','4' union

select 'C','T1','10' union

select 'D','T4','2' union

select 'E','空格','0'

--select * from T

--测试语句

DECLARE @SQL VARCHAR(8000)

SET @SQL='SELECT Item = isnull(Item,''SUM'')'



SELECT @SQL= @SQL+ ',max(CASE WHEN Type = ''' + Type + ''' THEN Qty else 0 END) [' + Type + ']' FROM T A GROUP BY Type



SET @SQL=@SQL+',TOTAL = SUM(Qty) FROM T GROUP BY Item WITH ROLLUP'

exec (@SQL)

 

--删除测试环境

Drop Table T



--建立测试环境

Create Table T(Item varchar(4),Type varchar(4),Qty integer)

--插入数据

insert into T

select 'A','T1','2' union

select 'A','T2','5' union

select 'B','T2','1' union

select 'B','T3','4' union

select 'C','T1','10' union

select 'D','T4','2' union

select 'E','','0' union

select 'F','','10' 

go

--查询处理

DECLARE @SQL VARCHAR(8000)

SET @SQL=''

SELECT @SQL= @SQL+ ','+quotename(case when Type='' then ' ' else Type end)

+'=isnull(sum(CASE Type when '+quotename(type,'''')+' THEN Qty END),0)'

FROM T A GROUP BY Type

exec('

select Item=case when grouping(Item)=1 then ''SUM'' else Item end'+@SQL+'

,TOTAL=sum(Qty) from T group by Item with rollup')

go

--删除测试环境

Drop Table T

/*--结果

Item             T1          T2          T3          T4          TOTAL       

---- ----------- ----------- ----------- ----------- ----------- ----------- 

A    0           2           5           0           0           7

B    0           0           1           4           0           5

C    0           10          0           0           0           10

D    0           0           0           0           2           2

E    0           0           0           0           0           0

F    10          0           0           0           0           10

SUM  10          12          6           4           2           34

---

这个问题第2个回答：

SQL code

  
  --静态SQL。

select id , 

  max(case px when 1 then num else null end) num1,

  max(case px when 2 then num else null end) num2,

  max(case px when 3 then num else null end) num3

from

(

  select * , px = (select count(1) from tb where id = t.id and num < t.num) + 1 from tb t

) m

group by id



--动态SQL。

declare @sql varchar(8000)

set @sql = 'select id '

select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then num else null end) [px' + cast(px as varchar) + ']'

from (select distinct px from (select * , px = (select count(1) from tb where id = t.id and num < t.num) + 1 from tb t) m) as a

set @sql = @sql + ' from (select * , px = (select count(1) from tb where id = t.id and num < t.num) + 1 from tb t) m group by id'

exec(@sql) 

---

这个问题第3个回答：

SQL code

  
   

/******************************************************************************/

/*回复：20080520003总：00032                                                   */

/*主题：动态行列转换                                                                     */

/*作者：二等草                                                                           */

/******************************************************************************/



set nocount on



--数据--------------------------------------------------------------------------

 

create table [A] ([id] varchar(3),[num] int)

 insert into [A] select '001',20012

 insert into [A] select '002',2321

 insert into [A] select '001',3625

 insert into [A] select '001',369

 insert into [A] select '002',363

go



--代码--------------------------------------------------------------------------

declare @i int,@sql varchar(1000),@j int

select xid=identity(int,1,1),* into # from a 

select @sql = 'select id'

select @j = 1,@i = max(c) from (select c=count(*) from a group by id) a

while @j<=@i

 begin

  select @sql =@sql+ char(10)+',sum(case when xh = '+rtrim(@j)+' then num  end) as num'+rtrim(@j)

  select @j = @j + 1

 end

set @sql = @sql + ' from (select *,xh=(select xh = count(*) from # where a.id = id and a.xid>=xid) 

                         from # a) b group by id'

exec(@sql)

drop table #

go



/*结果--------------------------------------------------------------------------

id   num1        num2        num3        

---- ----------- ----------- ----------- 

001  20012       3625        369

002  2321        363         NULL

--清除------------------------------------------------------------------------*/

drop table a