表一
A B
张三 30
张三 40
张三 50
李四 70
李四 80
李四 90
我要的结果是
表二
A B1 B2 B3
张三 30 40 50
李四 70 80 90还有如果何再把表二变成表一?
请各位大哥帮忙写一下SQL语句,谢谢。
A B
张三 30
张三 40
张三 50
李四 70
李四 80
李四 90
我要的结果是
表二
A B1 B2 B3
张三 30 40 50
李四 70 80 90还有如果何再把表二变成表一?
请各位大哥帮忙写一下SQL语句,谢谢。
调试欢乐多
(
A varchar(10),
B int
)insert A select '张三',30
insert A select '张三',40
insert A select '张三',50
insert A select '李四',70
insert A select '李四',80
insert A select '李四',90
select identity(int,1,1) as id,* into # from A
select A,
max(case when T_id=0 then B else 0 end) as A1,
max(case when T_id=1 then B else 0 end) as A2,
max(case when T_id=2 then B else 0 end) as A3
from
(select (select count(1) from # where T.A=A and T.id<=id)%(select count(1) from # where T.A=A) as T_id,T.A,T.B
from # T) M
group by A
create table A
(
A varchar(10),
B1 int,
B2 int,
B3 int
)insert A select '张三',30,40,50
insert A select '李四',70,80,90select * from
(select A,b1 from A
union all
select A,b2 from A
union all
select A,b3 from A) T
order by A
我想请教一下,如果张三有一万行,李四也有一万行的话,会不会要求打横为一万列了呢?
别难为人家啊
(
A varchar(10),
B int
)insert A select '张三',30
insert A select '张三',40
insert A select '张三',50
insert A select '李四',70
insert A select '李四',80
insert A select '李四',90
select identity(int,1,1) as id,* into # from Adeclare @T_SQL as varchar(8000)
set @T_SQL=''
select @T_SQL=@T_SQL + 'max(case when T_id=' + cast(T_id as varchar) + ' then B else 0 end) as A' + cast(T_id as varchar) +','
from
(select distinct (select count(1) from # where T.A=A and T.id<=id)%(select count(1) from # where T.A=A) as T_id
from
# T) M
select @T_SQL='select A,' + left(@T_SQL,len(@T_SQL)-1)+ ' from (select (select count(1) from # where T.A=A and T.id<=id)%(select count(1) from # where T.A=A) as T_id,T.A,T.B from # T) M group by A'exec (@T_SQL)