有表如下:
TABLE1(都是字符类型)
CODE NAME
1 AA
1 AB
1 AC
1 BB
2 CC
2 DD
3 AA
3 BB
3 CC
4 AC
4 AB
4 AD
4 BB
4 BC
4 BD
要求变成这样的:(按照最大的个数设置列,比如上例中CODE='4'时,共有6个NAME,所以生成6个COL(1.....6))
CODE COL1 COL2 COL3 COL4 COL5 COL6
1 AA AB AC BB
2 CC DD
3 AA BB CC
4 AC AB AD BB BC BD
有什么好的方法吗??
TABLE1(都是字符类型)
CODE NAME
1 AA
1 AB
1 AC
1 BB
2 CC
2 DD
3 AA
3 BB
3 CC
4 AC
4 AB
4 AD
4 BB
4 BC
4 BD
要求变成这样的:(按照最大的个数设置列,比如上例中CODE='4'时,共有6个NAME,所以生成6个COL(1.....6))
CODE COL1 COL2 COL3 COL4 COL5 COL6
1 AA AB AC BB
2 CC DD
3 AA BB CC
4 AC AB AD BB BC BD
有什么好的方法吗??
/*
问题:如果上述两表互相换一下:即表结构和数据为:
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果):
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (code varchar(11),name varchar(11))
insert into #1
select 1,'AA' union all
select 1,'AB' union all
select 1,'AC' union all
select 1,'BB' union all
select 2,'CC' union all
select 2,'DD' union all
select 3,'AA' union all
select 3,'BB' union all
select 3,'CC' union all
select 4,'AC' union all
select 4,'AB' union all
select 4,'AD' union all
select 4,'BB' union all
select 4,'BC' union all
select 4,'BD'declare @max int,@i int,@sql varchar(8000)
select top 1 @max=count(1), @i=1 from #1 group by code order by 1 descwhile @i<=@max
begin
set @sql=isnull(@sql+',','')+'col'+ltrim(@i)+'=max(case i when '+ltrim(@i)+' then name else '''' end)'
set @i=@i+1
endexec ('select code,'+@sql+' from (select i=(select count(1) from #1 where code=a.code and name<=a.name),* from #1 a) t group by code')/*
code col1 col2 col3 col4 col5 col6
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 AA AB AC BB
2 CC DD
3 AA BB CC
4 AB AC AD BB BC BD
*/
如小楼的 :)
insert into table1 select 1,'AA'
insert into table1 select 1,'AB'
insert into table1 select 1,'AC'
insert into table1 select 1,'BB'
insert into table1 select 2,'CC'
insert into table1 select 2,'DD'
insert into table1 select 3,'AA'
insert into table1 select 3,'BB'
insert into table1 select 3,'CC'
insert into table1 select 4,'AC'
insert into table1 select 4,'AB'
insert into table1 select 4,'AD'
insert into table1 select 4,'BB'
insert into table1 select 4,'BC'
insert into table1 select 4,'BD'DECLARE @SQL VARCHAR(1000)
set @sql='select code'select @sql=@sql+', max(case when px='''+ltrim(px)+''' then name else '''' end ) as col'+ltrim(px)
from
(
select distinct px=(row_number() over (partition by code order by code )) from table1
) aaset @sql=@sql+' from
(
select code,name, px=(row_number() over (partition by code order by code )) from table1
) aa group by code'exec(@sql)/*
code col1 col2 col3 col4 col5 col6
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 AA AB AC BB
2 CC DD
3 AA BB CC
4 AC AB AD BB BC BD(4 行受影响)
*/