有一表结构如下
id index xm xm1 xm2 hz
8 3 wdy 5.0 5.0 10.00
9 3 hzs 5.0 5.0 12.00
10 3 xjb 4.0 6.0 15.00
16 4 wdy 5.0 5.0 11.00
17 4 hzs 5.0 5.0 114.00
18 4 xjb 5.0 5.0 20.00
19 5 wdy 4.0 6.0 12.00
20 5 hzs 5.0 5.0 18.00
21 5 xjb 4.0 6.0 19.00
=====================================================================
现在要实现如下查询结果
index wdy hzs xjb
3 10.00 12.00 15.00
4 11.00 114.00 20.00
5 12.00 18.00 19.00
===========================================================================
高手们 如何实现!!!
id index xm xm1 xm2 hz
8 3 wdy 5.0 5.0 10.00
9 3 hzs 5.0 5.0 12.00
10 3 xjb 4.0 6.0 15.00
16 4 wdy 5.0 5.0 11.00
17 4 hzs 5.0 5.0 114.00
18 4 xjb 5.0 5.0 20.00
19 5 wdy 4.0 6.0 12.00
20 5 hzs 5.0 5.0 18.00
21 5 xjb 4.0 6.0 19.00
=====================================================================
现在要实现如下查询结果
index wdy hzs xjb
3 10.00 12.00 15.00
4 11.00 114.00 20.00
5 12.00 18.00 19.00
===========================================================================
高手们 如何实现!!!
hzs=max(case when xm='hzs' then hz else 0 end),
xjb=max(case when xm='xjb' then hz else 0 end)
from tb group by index
--======= 每天都在进步,却依然追不上地球的自传=========
--======= By: zc_0101 At:2009-08-12 14:40:47=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb (id int,[index] int,xm varchar(3),xm1 numeric(2,1),xm2 numeric(2,1),hz numeric(5,2))
insert into #tb
select 8,3,'wdy',5.0,5.0,10.00 union all
select 9,3,'hzs',5.0,5.0,12.00 union all
select 10,3,'xjb',4.0,6.0,15.00 union all
select 16,4,'wdy',5.0,5.0,11.00 union all
select 17,4,'hzs',5.0,5.0,114.00 union all
select 18,4,'xjb',5.0,5.0,20.00 union all
select 19,5,'wdy',4.0,6.0,12.00 union all
select 20,5,'hzs',5.0,5.0,18.00 union all
select 21,5,'xjb',4.0,6.0,19.00select * from #tb
----------------查询------------
select [index],
sum(case when xm='wdy' then hz else 0 end) as 'xm',
sum(case when xm='hzs' then hz else 0 end) as 'hzs',
sum(case when xm='xjb' then hz else 0 end) as 'xjb'
from #tb group by [index]
----------------结果--------------
/*
index xm hzs xjb
3 10.00 12.00 15.00
4 11.00 114.00 20.00
5 12.00 18.00 19.00
*/
select [index],
wdy=max(case when xm='wdy' then hz end),
hzs=max(case when xm='hzs' then hz end),
xjb=max(case when xm='xjb' then hz end)
from tb
group by [index]
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-12 14:48:03
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[index] int,[xm] varchar(3),[xm1] numeric(2,1),[xm2] numeric(2,1),[hz] numeric(5,2))
insert [tb]
select 8,3,'wdy',5.0,5.0,10.00 union all
select 9,3,'hzs',5.0,5.0,12.00 union all
select 10,3,'xjb',4.0,6.0,15.00 union all
select 16,4,'wdy',5.0,5.0,11.00 union all
select 17,4,'hzs',5.0,5.0,114.00 union all
select 18,4,'xjb',5.0,5.0,20.00 union all
select 19,5,'wdy',4.0,6.0,12.00 union all
select 20,5,'hzs',5.0,5.0,18.00 union all
select 21,5,'xjb',4.0,6.0,19.00
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select [index]'
select @sql = @sql + ' , sum(case xm when ''' + xm + ''' then hz else 0 end) [' + xm + ']'
from (select distinct xm from tb) as a
set @sql = @sql + ' from tb group by [index]'
exec(@sql)
----------------结果----------------------------
/*index hzs wdy xjb
----------- ---------------------------------------- ---------------------------------------- ----------------------------------------
3 12.00 10.00 15.00
4 114.00 11.00 20.00
5 18.00 12.00 19.00*/
[/code]
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-12 14:48:03
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[index] int,[xm] varchar(3),[xm1] numeric(2,1),[xm2] numeric(2,1),[hz] numeric(5,2))
insert [tb]
select 8,3,'wdy',5.0,5.0,10.00 union all
select 9,3,'hzs',5.0,5.0,12.00 union all
select 10,3,'xjb',4.0,6.0,15.00 union all
select 16,4,'wdy',5.0,5.0,11.00 union all
select 17,4,'hzs',5.0,5.0,114.00 union all
select 18,4,'xjb',5.0,5.0,20.00 union all
select 19,5,'wdy',4.0,6.0,12.00 union all
select 20,5,'hzs',5.0,5.0,18.00 union all
select 21,5,'xjb',4.0,6.0,19.00
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select [index]'
select @sql = @sql + ' , sum(case xm when ''' + xm + ''' then hz else 0 end) [' + xm + ']'
from (select distinct xm from tb) as a
set @sql = @sql + ' from tb group by [index]'
exec(@sql)
----------------结果----------------------------
/*index hzs wdy xjb
----------- ---------------------------------------- ---------------------------------------- ----------------------------------------
3 12.00 10.00 15.00
4 114.00 11.00 20.00
5 18.00 12.00 19.00*/
[/code]