sql 行转列问题 case when 条件不确定

backhead 2006-12-20 10:31:16
表是这样的一个 需要行转列
create table aaa (id int,id2 int,num varchar(4))
go
insert into aaa select 1,2,'a'
union all select 1,3,'b'
union all select 1,5,'c'
union all select 1,8,'d'
union all select 2,9,'e'
union all select 2,11,'f'
union all select 2,23,'g'
union all select 2,24,'h'
union all select 4,23,'i'

select * from aaa
go
drop table aaa

---------------------------
想要的结果是
id id2x1 id2x2 id2x3 id2x4
1 a b c d
2 e f g h
4 i

就是把id相同 id2不相同 组成一行。。
如果是平时的 case when id2= xx 就可以。现在id2不是固定的。只是想把id相同的横向显示一下 id2x1 列就是第一行id=2的数据,id2x2 列就是第二行id=2的数据,
id3x1 列就是第三行id=2的数据。
最多不会超过7列。
...全文
396 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
marco08 2006-12-20
  • 打赏
  • 举报
回复
--改改, 上面的少了一句
--創建表
create table aaa (id int, id2 int, num varchar(4))
go
insert into aaa select 1,2,'a'
union all select 1,3,'b'
union all select 1,5,'c'
union all select 1,8,'d'
union all select 2,9,'e'
union all select 2,11,'f'
union all select 2,23,'g'
union all select 2,24,'h'
union all select 4,23,'i'
--創建臨時表
select *, id3=(select count(*) from aaa where id=A.id and id2<=A.id2) into #T from aaa as A
--生成動態SQL
declare @sql varchar(8000)
set @sql='select id,'
select @sql=@sql+quotename('id2X'+rtrim(id3))+'=max(case when id3='+rtrim(id3)+' then num else '''' end),' from #T group by id3
select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from #T group by id '
exec(@sql)
--result
id id2X1 id2X2 id2X3 id2X4
----------- ----- ----- ----- -----
1 a b c d
2 e f g h
4 i
--刪除表
drop table aaa
drop table #T
marco08 2006-12-20
  • 打赏
  • 举报
回复
create table aaa (id int, id2 int, num varchar(4))
go
insert into aaa select 1,2,'a'
union all select 1,3,'b'
union all select 1,5,'c'
union all select 1,8,'d'
union all select 2,9,'e'
union all select 2,11,'f'
union all select 2,23,'g'
union all select 2,24,'h'
union all select 4,23,'i'

declare @sql varchar(8000)
set @sql='select id,'
select @sql=@sql+quotename('id2X'+rtrim(id3))+'=max(case when id3='+rtrim(id3)+' then num else '''' end),' from #T group by id3
select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from #T group by id '
exec(@sql)

--result
id id2X1 id2X2 id2X3 id2X4
----------- ----- ----- ----- -----
1 a b c d
2 e f g h
4 i
wzh1215 2006-12-20
  • 打赏
  • 举报
回复
create table aaa(id int,id2 int,num varchar(4))
insert into aaa select 1,2,'a'
union all select 1,3,'b'
union all select 1,5,'c'
union all select 1,8,'d'
union all select 2,9,'e'
union all select 2,11,'f'
union all select 2,23,'g'
union all select 2,24,'h'
union all select 4,23,'i'
create table #aaa (id int,id2 int,num varchar(4),ids int)
insert #aaa(id,id2,num)
select * from aaa order by id
Go
declare @ids int,@id int
update #aaa
set ids=@ids,@ids=(case id when @id then @ids+1 else 1 end),@id=id
Go
declare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ',max(case ids when '''+cast(ids as varchar(10))+''' then num else '''' end) [id2x'+cast(ids as varchar(10))+']'
from (select distinct ids from #aaa) as a
select @sql = @sql+' from #aaa group by id'
select @sql
exec(@sql)
子陌红尘 2006-12-20
  • 打赏
  • 举报
回复
create table aaa (id int,id2 int,num varchar(4))
go
insert into aaa select 1,2,'a'
union all select 1,3,'b'
union all select 1,5,'c'
union all select 1,8,'d'
union all select 2,9,'e'
union all select 2,11,'f'
union all select 2,23,'g'
union all select 2,24,'h'
union all select 4,23,'i'
go

declare @i int,@sql varchar(8000)
select @i=max(num),@sql='' from (select count(*) as num from aaa group by id) t

while @i>0
select @sql= ',id2x'+rtrim(@i)
+'=isnull(max(case i when '+rtrim(@i)+' then num end),'''')'
+@sql,
@i = @i-1

set @sql='select
id'+@sql+'
from
(select
a.*,
(select count(*) from aaa where id=a.id and id2<=a.id2) as i
from
aaa a) t
group by
id'
exec(@sql)
go

/*
id id2x1 id2x2 id2x3 id2x4
----------- ----- ----- ----- -----
1 a b c d
2 e f g h
4 i
*/

drop table aaa
go
子陌红尘 2006-12-20
  • 打赏
  • 举报
回复
create table aaa (id int,id2 int,num varchar(4))
go
insert into aaa select 1,2,'a'
union all select 1,3,'b'
union all select 1,5,'c'
union all select 1,8,'d'
union all select 2,9,'e'
union all select 2,11,'f'
union all select 2,23,'g'
union all select 2,24,'h'
union all select 4,23,'i'
go

declare @i int,@sql varchar(8000)
select @i=max(num),@sql='' from (select count(*) as num from aaa group by id) t

while @i>0
begin
select @sql=',id2x'+rtrim(@i)+'=max(case i when '+rtrim(@i)+' then num end)'+@sql,@i=@i-1
end

set @sql='select id'+@sql+' from (select *,(select count(*) from aaa where id=a.id and id2<=a.id2) as i from aaa a) t group by id'


exec(@sql)
go

/*
id id2x1 id2x2 id2x3 id2x4
----------- ----- ----- ----- -----
1 a b c d
2 e f g h
4 i NULL NULL NULL
*/

drop table aaa
go

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧