各位大侠:如何将表
2012-5-22 21:09:00 23 YD78FC 代景平
2012-5-22 21:09:08 89 YD78FC 代景平
2012-5-22 21:09:16 567 YD78FC 代景平转换成2012-5-22 21:09:00 2012-5-22 21:09:08 2012-5-22 21:09:16
23 89 567
YD78FC YD78FC YD78FC
代景平 代景平 代景平
谢谢大侠!
2012-5-22 21:09:00 23 YD78FC 代景平
2012-5-22 21:09:08 89 YD78FC 代景平
2012-5-22 21:09:16 567 YD78FC 代景平转换成2012-5-22 21:09:00 2012-5-22 21:09:08 2012-5-22 21:09:16
23 89 567
YD78FC YD78FC YD78FC
代景平 代景平 代景平
谢谢大侠!
http://topic.csdn.net/u/20090407/11/c7bd7003-d4da-41a2-9521-404e577dd549.html
create table m88(A varchar(30), B varchar(30), C varchar(30), D varchar(30))insert into m88
select '2012-5-22 21:09:00', '23', 'YD78FC', '代景平' union all
select '2012-5-22 21:09:08', '89', 'YD78FC', '代景平' union all
select '2012-5-22 21:09:16', '567', 'YD78FC', '代景平'select * from m88/*
A B C D
------------------------------ ------------------------------ ------------------------------ ------------------------------
2012-5-22 21:09:00 23 YD78FC 代景平
2012-5-22 21:09:08 89 YD78FC 代景平
2012-5-22 21:09:16 567 YD78FC 代景平(3 row(s) affected)
*/
select [1],[2],[3] from
(select rn,c1,c2
from
(select row_number() over(order by (select 0)) rn,B,C,D,A from m88) t
unpivot (c1 for c2 in (B,C,D,A)) u) x
pivot(max(c1) for rn IN ([1],[2],[3])) t/*
1 2 3
------------------------------ ------------------------------ ------------------------------
2012-5-22 21:09:00 2012-5-22 21:09:08 2012-5-22 21:09:16
23 89 567
YD78FC YD78FC YD78FC
代景平 代景平 代景平(4 row(s) affected)
*/
(A varchar(30), B varchar(30), C varchar(30), D varchar(30))insert into m88
select '2012-5-22 21:09:00', '23', 'YD78FC', '代景平' union all
select '2012-5-22 21:09:08', '89', 'YD78FC', '代景平' union all
select '2012-5-22 21:09:16', '567', 'YD78FC', '代景平'
select identity(int,1,1) rn, A,B,C,D
into #t from m88select max([1]) [1],max([2]) [2],max([3]) [3]
from(select
case when rn=1 then max(A) end '1',
case when rn=2 then max(A) end '2',
case when rn=3 then max(A) end '3'
from #t group by rn) ta
union all
select max([1]) [1],max([2]) [2],max([3]) [3]
from(select
case when rn=1 then max(B) end '1',
case when rn=2 then max(B) end '2',
case when rn=3 then max(B) end '3'
from #t group by rn) tb
union all
select max([1]) [1],max([2]) [2],max([3]) [3]
from(select
case when rn=1 then max(C) end '1',
case when rn=2 then max(C) end '2',
case when rn=3 then max(C) end '3'
from #t group by rn) tc
union all
select max([1]) [1],max([2]) [2],max([3]) [3]
from(select
case when rn=1 then max(D) end '1',
case when rn=2 then max(D) end '2',
case when rn=3 then max(D) end '3'
from #t group by rn) td/*
1 2 3
------------------------------ ------------------------------ ------------------------------
2012-5-22 21:09:00 2012-5-22 21:09:08 2012-5-22 21:09:16
23 89 567
YD78FC YD78FC YD78FC
代景平 代景平 代景平(4 row(s) affected)
*/
你好!您给的 很有帮助,但是还有一点。我的数据有1-27行,不确定有多少行!我不熟悉,期待您给个2000的答案。谢谢。只有“YD78FC” 这一列绝对的相同,其他列可能不相同。谢谢!!!!拜托!!
只有“YD78FC”是确定的值,其他的时间,姓名之类的 可能都不相同。拜托。sql2000。
谢谢大侠!
create table m88
(A varchar(30), B varchar(30), C varchar(30), D varchar(30))insert into m88
select '2012-5-22 21:09:00', '23', 'YD78FC', '代景平' union all
select '2012-5-22 21:09:08', '89', 'YD78FC', '代景平' union all
select '2012-5-22 21:09:16', '567', 'YD78FC', '代景平' union all
select '2012-05-23 14:38:44', '40', 'YD78FC', '楼主甲' union all
select '2012-05-23 14:41:50', '40', 'YD78FC', '楼主乙'
select identity(int,1,1) rn, A,B,C,D
into #t from m88
declare @sql varchar(max)='',@s1 varchar(6000)='',@s2 varchar(6000)=''
select @s1=@s1+',case when rn='+cast(rn as varchar(5))+' then max(X) end '''+cast(rn as varchar(5))+''' ' from #t
select @s2=@s2+',max(['+cast(rn as varchar(5))+']) ['+cast(rn as varchar(5))+']' from #tselect @sql='select '+stuff(@s2,1,1,'')
+' from(select '+replace(stuff(@s1,1,1,''),'max(X)','max(A)')
+' from #t group by rn) ta '+char(10)+' union all '+char(10)select @sql=@sql+'select '+stuff(@s2,1,1,'')
+' from(select '+replace(stuff(@s1,1,1,''),'max(X)','max(B)')
+' from #t group by rn) tb '+char(10)+' union all '+char(10)select @sql=@sql+'select '+stuff(@s2,1,1,'')
+' from(select '+replace(stuff(@s1,1,1,''),'max(X)','max(C)')
+' from #t group by rn) tc '+char(10)+' union all '+char(10)select @sql=@sql+'select '+stuff(@s2,1,1,'')
+' from(select '+replace(stuff(@s1,1,1,''),'max(X)','max(D)')
+' from #t group by rn) td 'exec(@sql)/*
1 2 3 4 5
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
2012-5-22 21:09:00 2012-5-22 21:09:08 2012-5-22 21:09:16 2012-05-23 14:38:44 2012-05-23 14:41:50
23 89 567 40 40
YD78FC YD78FC YD78FC YD78FC YD78FC
代景平 代景平 代景平 楼主甲 楼主乙(4 row(s) affected)
*/