22,207
社区成员
发帖
与我相关
我的任务
分享行列转换
create table test
(
A nvarchar(30),
B int,
C nvarchar(30),
D nvarchar(30),
E nvarchar(30),
)
insert into test (A,B,C,D,E) values ('k',1,'a','b','c')
insert into test (A,B,C,D,E) values ('k',1,'a','d','e')
insert into test (A,B,C,D,E) values ('k',1,'a','f','g')
insert into test (A,B,C,D,E) values ('k',1,'a','h','i')
insert into test (A,B,C,D,E) values ('k',1,'a','J','t')
select * From test
表结果是这样的
k 1 a b c
k 1 a d e
k 1 a f g
k 1 a h i
k 1 a J t
想要查询出来的结果是 以 A B C 分组后 D E 两列的值作为 5 个字段 排开
结果想要
A B C D1 D2 D3 D4 D5 D6 D7 D8 D9 D10
k 1 a b c d e f g h i J t
...
....
....
...全文
请发表友善的回复…
发表回复
筱筱澄 2012-07-04
- 打赏
- 举报
create table test
(
A nvarchar(30),
B int,
C nvarchar(30),
D nvarchar(30),
E nvarchar(30),
)
insert into test (A,B,C,D,E) values ('k',1,'a','b','c')
insert into test (A,B,C,D,E) values ('k',1,'a','d','e')
insert into test (A,B,C,D,E) values ('k',1,'a','f','g')
insert into test (A,B,C,D,E) values ('k',1,'a','h','i')
insert into test (A,B,C,D,E) values ('k',1,'a','J','t')
declare @sql varchar(max)
select @sql=isnull(@sql+',','')
+'max(case when row='+ltrim(row)+' then d else null end) as [D'+ltrim(row)+'],'
+'max(case when row='+ltrim(row)+' then e else null end) as [E'+ltrim(row)+']'
from
(
select *,row=(select count(*) from test where a=t.a and b=t.b and c=t.c and d<=t.d) from test as t
) as t1
select @sql='select a,b,c,'+@sql+'
from
(
select *,row=(select count(*) from test where a=t.a and b=t.b and c=t.c and d<=t.d) from test as t
) as t1
group by a,b,c
'e
exec( @sql)
/*
a b c D1 E1 D2 E2 D3 E3 D4 E4 D5 E5
------------------------------ ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
k 1 a b c d e f g h i J t
*/
开着拖拉机泡妞 2012-07-04
- 打赏
- 举报
create table test
(
A nvarchar(30),
B int,
C nvarchar(30),
D nvarchar(30),
E nvarchar(30),
)
insert into test (A,B,C,D,E) values ('k',1,'a','b','c')
insert into test (A,B,C,D,E) values ('k',1,'a','d','e')
insert into test (A,B,C,D,E) values ('k',1,'a','f','g')
insert into test (A,B,C,D,E) values ('k',1,'a','h','i')
insert into test (A,B,C,D,E) values ('k',1,'a','J','t')
alter table test add id int identity
go
declare @str varchar(2000)
set @str=''
select
@str=@str+',[D'+LTRIM(id)+']=max(case when id='
+LTRIM(id)+' then D else '''' end),'
+'[E'+ltrim(id+(select count(1) from test))+']=max(case when id='
+LTRIM(id)+' then E else '''' end)'
from
test
group by
id
order by
id
exec('select A,B,C'+@str+' from test group by A,B,C')
/*
A B C D1 E6 D2 E7 D3 E8 D4 E9 D5 E10
k 1 a b c d e f g h i J t
*/
alter table test drop column id
go
百年树人 2012-07-04
- 打赏
- 举报
[Quote=引用 2 楼 的回复:]
引用 1 楼 的回复:
SQL code
declare @sql varchar(max)
select
@sql=isnull(@sql+',','')
+'max(case when rn='+ltrim(rn)+' then d end) as [D'+ltrim(rn*2-1)+'],'
+'max(case when rn='+ltrim(rn)+' then e end)……
谢谢 ，但是 能有别的办法替代 row_number() 2000 不兼容啊
[/Quote]
由于没有行号，sql2000需要借助一个临时表
引用 1 楼 的回复:
SQL code
declare @sql varchar(max)
select
@sql=isnull(@sql+',','')
+'max(case when rn='+ltrim(rn)+' then d end) as [D'+ltrim(rn*2-1)+'],'
+'max(case when rn='+ltrim(rn)+' then e end)……
谢谢 ，但是 能有别的办法替代 row_number() 2000 不兼容啊
[/Quote]
由于没有行号，sql2000需要借助一个临时表
select *,tid=identity(int,1,1) into # from test
go
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'max(case when rn='+ltrim(rn)+' then d end) as [D'+ltrim(rn*2-1)+'],'
+'max(case when rn='+ltrim(rn)+' then e end) as [D'+ltrim(rn*2)+']'
from(
select distinct rn=(select count(1) from # where a=t.a and b=t.b and c=t.c and tid<=t.tid) from # t
) t2
exec ('select a,b,c,'+@sql+' from ('
+'select *,rn=(select count(1) from # where a=t.a and b=t.b and c=t.c and tid<=t.tid) from # t) t2'
+' group by a,b,c'
)
屌丝女士111 2012-07-04
- 打赏
- 举报
[Quote=引用 1 楼 的回复:]
SQL code
declare @sql varchar(max)
select
@sql=isnull(@sql+',','')
+'max(case when rn='+ltrim(rn)+' then d end) as [D'+ltrim(rn*2-1)+'],'
+'max(case when rn='+ltrim(rn)+' then e end) as [D……
[/Quote]
谢谢 ，但是 能有别的办法替代 row_number() 2000 不兼容啊
SQL code
declare @sql varchar(max)
select
@sql=isnull(@sql+',','')
+'max(case when rn='+ltrim(rn)+' then d end) as [D'+ltrim(rn*2-1)+'],'
+'max(case when rn='+ltrim(rn)+' then e end) as [D……
[/Quote]
谢谢 ，但是 能有别的办法替代 row_number() 2000 不兼容啊
百年树人 2012-07-04
- 打赏
- 举报
declare @sql varchar(max)
select
@sql=isnull(@sql+',','')
+'max(case when rn='+ltrim(rn)+' then d end) as [D'+ltrim(rn*2-1)+'],'
+'max(case when rn='+ltrim(rn)+' then e end) as [D'+ltrim(rn*2)+']'
from(
select distinct rn=row_number() over(partition by a,b,c order by getdate()) from test
) t
exec ('select a,b,c,'+@sql+' from ('
+'select *,rn=row_number() over(partition by a,b,c order by getdate()) from test) t'
+' group by a,b,c'
)
/**
a b c D1 D2 D3 D4 D5 D6 D7 D8 D9 D10
------------------------------ ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
k 1 a b c d e f g h i J t
(1 行受影响)
**/
