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

来源：互联网发布：微软必应词典 mac 编辑：程序博客网时间：2024/04/28 19:48

现在有这样一个记录集：
   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 Tselect 'A','T1','2' unionselect 'A','T2','5' unionselect 'B','T2','1' unionselect 'B','T3','4' unionselect 'C','T1','10' unionselect 'D','T4','2' unionselect '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 TypeSET @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 Tselect 'A','T1','2' unionselect 'A','T2','5' unionselect 'B','T2','1' unionselect 'B','T3','4' unionselect 'C','T1','10' unionselect 'D','T4','2' unionselect 'E','','0' unionselect '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 Typeexec('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           7B    0           0           1           4           0           5C    0           10          0           0           0           10D    0           0           0           0           2           2E    0           0           0           0           0           0F    10          0           0           0           0           10SUM  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) num3from(  select * , px = (select count(1) from tb where id = t.id and num < t.num) + 1 from tb t) mgroup 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 aset @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',363go--代码--------------------------------------------------------------------------declare @i int,@sql varchar(1000),@j intselect 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) awhile @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 endset @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        369002  2321        363         NULL--清除------------------------------------------------------------------------*/drop table a