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