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

时间:2020-12-16 15:12:47
  现在有这样一个记录集:
   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个回答:

行列转换例子参考
  
  
 
 --建立测试环境

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。

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个回答:
  
  
 
  

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

/*回复: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