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

ゝ一世哀愁。 2022-08-23 14:47 156阅读 0赞

现在有这样一个记录集：  
   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