--参照老大写的
--http://community.csdn.net/Expert/topic/4836/4836204.xml?temp=.6176264
--修改而得if object_id('bmk') is not null drop table bmk
go
create table bmk
(
id int,
KH_D int,
KM_H int,
KS_H int
)
insert into bmk
select 1,244,2,1001 union all
select 2,342,4,1002 union all
select 3,423,3,1001 union all
select 4,342,10,1003select * from bmkdeclare @count int,@sql varchar(8000)
select top 1 @count=count(KS_H),@sql='' from bmk group by KS_H order by count(KS_H) descwhile @count>0
begin
select @sql=',a'+rtrim(2*@count-1)+'=max(case row when '+rtrim(@count)+' then KM_H else null end),a'+rtrim(2*@count)+'=max(case row when '+rtrim(@count)+' then KH_D else null end)'+@sql
set @count=@count-1
end
select @sql='select name'+@sql+' from (select name=KS_H,KM_H,KH_D,row=(select count(*) from bmk where KS_H=a.KS_H and id<=a.id) from bmk a)aa group by name'
exec (@sql)drop table tb/*id KH_D KM_H KS_H
----------- ----------- ----------- -----------
1 244 2 1001
2 342 4 1002
3 423 3 1001
4 342 10 1003name a1 a2 a3 a4
----------- ----------- ----------- ----------- -----------
1001 2 244 3 423
1002 4 342 NULL NULL
1003 10 342 NULL NULL不知道你准备插入的ID列是怎么来的,所以没有加入
*/
--http://community.csdn.net/Expert/topic/4836/4836204.xml?temp=.6176264
--修改而得if object_id('bmk') is not null drop table bmk
go
create table bmk
(
id int,
KH_D int,
KM_H int,
KS_H int
)
insert into bmk
select 1,244,2,1001 union all
select 2,342,4,1002 union all
select 3,423,3,1001 union all
select 4,342,10,1003select * from bmkdeclare @count int,@sql varchar(8000)
select top 1 @count=count(KS_H),@sql='' from bmk group by KS_H order by count(KS_H) descwhile @count>0
begin
select @sql=',a'+rtrim(2*@count-1)+'=max(case row when '+rtrim(@count)+' then KM_H else null end),a'+rtrim(2*@count)+'=max(case row when '+rtrim(@count)+' then KH_D else null end)'+@sql
set @count=@count-1
end
select @sql='select name'+@sql+' from (select name=KS_H,KM_H,KH_D,row=(select count(*) from bmk where KS_H=a.KS_H and id<=a.id) from bmk a)aa group by name'
exec (@sql)drop table tb/*id KH_D KM_H KS_H
----------- ----------- ----------- -----------
1 244 2 1001
2 342 4 1002
3 423 3 1001
4 342 10 1003name a1 a2 a3 a4
----------- ----------- ----------- ----------- -----------
1001 2 244 3 423
1002 4 342 NULL NULL
1003 10 342 NULL NULL不知道你准备插入的ID列是怎么来的,所以没有加入
*/
解决方案 »
- 这个查询应该怎么做啊?
- 怎样取到这个值?
- 弱问个初学者问题-同时将数据写到若干个表中
- 求SQL语句一条,在线等!
- 求:主键加上和去掉identity属性的语句
- 在数据库查询里有遇到这种情况吗???
- 一个有趣的SQL语句,各位进来看看。
- 如何用SQL语句修改字段长度?
- 有密码保护的access意外退出,结果下一次进入时怎么也打不开,怎么打开?或怎么取消密码保护?
- 这个SQL如何写?
- 为什么会出现这种情况 未能处理对象 'select * from d.dbf'。OLE DB 提供程序 'MICROSOFT.JET.OLEDB.4.0' 指出该对象中没有任何列。
- 这个是我感觉非常麻烦的一个查询,谢谢解答
改为
drop table bmk
写顺手了^^;a.....可以根据你原有数据的组成自动扩增,测试数据只能自动生成到a4
SELECT id = 1,
name,
a1=MAX(CASE row WHEN 1 THEN KM_H ELSE NULL END),
a2=MAX(CASE row WHEN 1 THEN KH_D ELSE NULL END),
a3=MAX(CASE row WHEN 2 THEN KM_H ELSE NULL END),
a4=MAX(CASE row WHEN 2 THEN KH_D ELSE NULL END),
a5=MAX(CASE row WHEN 3 THEN KM_H ELSE NULL END),
a6=MAX(CASE row WHEN 3 THEN KH_D ELSE NULL END),
a7=MAX(CASE row WHEN 4 THEN KM_H ELSE NULL END),
a8=MAX(CASE row WHEN 4 THEN KH_D ELSE NULL END),
a9=MAX(CASE row WHEN 5 THEN KM_H ELSE NULL END),
a10=MAX(CASE row WHEN 5 THEN KH_D ELSE NULL END),
a11=MAX(CASE row WHEN 6 THEN KM_H ELSE NULL END),
a12=MAX(CASE row WHEN 6 THEN KH_D ELSE NULL END),
a13=MAX(CASE row WHEN 7 THEN KM_H ELSE NULL END),
a14=MAX(CASE row WHEN 7 THEN KH_D ELSE NULL END),
a15=MAX(CASE row WHEN 8 THEN KM_H ELSE NULL END),
a16=MAX(CASE row WHEN 8 THEN KH_D ELSE NULL END),
a17=MAX(CASE row WHEN 9 THEN KM_H ELSE NULL END),
a18=MAX(CASE row WHEN 9 THEN KH_D ELSE NULL END),
a19=MAX(CASE row WHEN 10 THEN KM_H ELSE NULL END),
a20=MAX(CASE row WHEN 10 THEN KH_D ELSE NULL END),
a21=MAX(CASE row WHEN 11 THEN KM_H ELSE NULL END),
a22=MAX(CASE row WHEN 11 THEN KH_D ELSE NULL END),
a23=MAX(CASE row WHEN 12 THEN KM_H ELSE NULL END),
a24=MAX(CASE row WHEN 12 THEN KH_D ELSE NULL END)
FROM(
SELECT name=KS_H,KM_H,KH_D,
row=(select COUNT(*) FROM bmk WHERE KS_H=A.KS_H AND id<=A.id)
FROM bmk A
)AA GROUP BY name