原数据:ID 类型 值
A 11 2000
A 12 2400
A 13 2900
A 14 3000
B 11 9000
B 12 2000
B 13 2400
B 14 2900
C 11 3000
C 12 9000写一sql语句,使之成为如下数据:
ID 值1 值2 值3 值4
A 2000 2400 2900 3000
B 9000 2000 2400 2900
C 3000 9000
A 11 2000
A 12 2400
A 13 2900
A 14 3000
B 11 9000
B 12 2000
B 13 2400
B 14 2900
C 11 3000
C 12 9000写一sql语句,使之成为如下数据:
ID 值1 值2 值3 值4
A 2000 2400 2900 3000
B 9000 2000 2400 2900
C 3000 9000
解决方案 »
- 请教分布式查询高手
- 如何用文本文件写存储过程以及触发器
- 请教一句sql语句!!
- 这段SQL语句有没有错误,怎么使用呢?
- 求助!
- 正在学习存储过程,Use sp_executesql instead of EXECUTE to run Transact-SQL strings in your stored procedures.这句话什么意思?
- 请问,在某个用户修改Access数据库时,会对其他使户的使用有哪些
- 触发器 trigger应用,请教怎么用
- 标识会自动跳过一些数字,急,在线等!!!
- 怎样对SQL Server 的Bit 数据类型赋值?
- SQL SERVER2005如何将如一内容例“123456789”,在指定数据库中查找相关表
- instead of 触发器和 after触发器有什么区别???
id,
max(case 类型 when 11 then 值 else 0 end) as 值1,
max(case 类型 when 12 then 值 else 0 end) as 值2,
max(case 类型 when 13 then 值 else 0 end) as 值3,
max(case 类型 when 14 then 值 else 0 end) as 值4
from
tb
group by
id
max(case px when 1 then cast(值 as varchar) else '') 值1,
max(case px when 2 then cast(值 as varchar) else '') 值2,
max(case px when 3 then cast(值 as varchar) else '') 值3,
max(case px when 4 then cast(值 as varchar) else '') 值4
from
(
select t.* , px = (select count(1) from tb where id = t.id and 值 < t.值) + 1 from tb t
) m
group by id
insert into tb select 'A',11,2000
insert into tb select 'A',12,2400
insert into tb select 'A',13,2900
insert into tb select 'A',14,3000
insert into tb select 'B',11,9000
insert into tb select 'B',12,2000
insert into tb select 'B',13,2400
insert into tb select 'B',14,2900
insert into tb select 'C',11,3000
insert into tb select 'C',12,9000
go
select id,sum(case when 类型=11 then 值 else 0 end)值1,
sum(case when 类型=12 then 值 else 0 end)值2,
sum(case when 类型=13 then 值 else 0 end)值3,
sum(case when 类型=14 then 值 else 0 end)值4
from tb group by id
/*
id 值1 值2 值3 值4
---------- ----------- ----------- ----------- -----------
A 2000 2400 2900 3000
B 9000 2000 2400 2900
C 3000 9000 0 0(3 行受影响)*/
go
drop table tb
insert into tb values('A', 11 ,2000)
insert into tb values('A', 12 ,2400)
insert into tb values('A', 13 ,2900)
insert into tb values('A', 14 ,3000)
insert into tb values('B', 11 ,9000)
insert into tb values('B', 12 ,2000)
insert into tb values('B', 13 ,2400)
insert into tb values('B', 14 ,2900)
insert into tb values('C', 11 ,3000)
insert into tb values('C', 12 ,9000)
go--如果你根据类型来算
select id ,
max(case 类型 when 11 then cast(值 as varchar) else '' end) 值1,
max(case 类型 when 12 then cast(值 as varchar) else '' end) 值2,
max(case 类型 when 13 then cast(值 as varchar) else '' end) 值3,
max(case 类型 when 14 then cast(值 as varchar) else '' end) 值4
from tb
group by id
/*
id 值1 值2 值3 值4
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
A 2000 2400 2900 3000
B 9000 2000 2400 2900
C 3000 9000 (所影响的行数为 3 行)
*/--如果你根据ID其值大小来算
select id ,
max(case px when 1 then cast(值 as varchar) else '' end) 值1,
max(case px when 2 then cast(值 as varchar) else '' end) 值2,
max(case px when 3 then cast(值 as varchar) else '' end) 值3,
max(case px when 4 then cast(值 as varchar) else '' end) 值4
from
(
select t.* , px = (select count(1) from tb where id = t.id and 值 < t.值) + 1 from tb t
) m
group by id
/*
id 值1 值2 值3 值4
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
A 2000 2400 2900 3000
B 9000 2000 2400 2900
C 3000 9000 (所影响的行数为 3 行)
*/drop table tb
max(case 类型 when 11 then cast(值 as varchar) else '' end) 值1,
max(case 类型 when 12 then cast(值 as varchar) else '' end) 值2,
max(case 类型 when 13 then cast(值 as varchar) else '' end) 值3,
max(case 类型 when 14 then cast(值 as varchar) else '' end) 值4
from #tb
group by id