请教下面一个问题
表afName fValue FmployeeID FDate
基本工资 1000 1 2010-1-1
奖金 1000 1 2010-1-1
补助 500 1 2010-1-1
-------------------------------------------------
我想在gridview上显示姓名 基本工资 奖金 补助 时间
张三 1000 1000 500 2010-1-1怎么写
-------------------------------
fname不固定 数据一月只能输入一次!
表afName fValue FmployeeID FDate
基本工资 1000 1 2010-1-1
奖金 1000 1 2010-1-1
补助 500 1 2010-1-1
-------------------------------------------------
我想在gridview上显示姓名 基本工资 奖金 补助 时间
张三 1000 1000 500 2010-1-1怎么写
-------------------------------
fname不固定 数据一月只能输入一次!
http://blog.csdn.net/robinson_0612/archive/2010/03/16/5385117.aspx
set @sql = 'select [FmployeeID] '
select @sql = @sql + ' , max(case fValue when ''' + ltrim(fValue) + ''' then fName else '' end) [' + ltrim(fValue) + ']'
from (select distinct fValue from tb) as a
set @sql = @sql + ' from tb group by FmployeeID'
exec(@sql)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-29 16:09:40
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([fName] varchar(8),[fValue] int,[FmployeeID] int,[FDate] datetime)
insert [tb]
select '基本工资',1000,1,'2010-1-1' union all
select '奖金',1000,1,'2010-1-1' union all
select '补助',500,1,'2010-1-1'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select [FmployeeID] '
select @sql = @sql + ' , max(case fName when ''' + fName + ''' then fValue else '''' end) [' + fName + ']'
from (select distinct fName from tb) as a
set @sql = @sql + ' from tb group by FmployeeID'
exec(@sql)
----------------结果----------------------------
/*FmployeeID 补助 基本工资 奖金
----------- ----------- ----------- -----------
1 500 1000 1000(1 行受影响)
*/
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
fName char(9),
fValue int,
FmployeeID int,
FDate datetime
)
go
--插入测试数据
insert into tb select '基本工资',1000,1,'2010-1-1'
union all select '奖金',1000,1,'2010-1-1'
union all select '补助',500,1,'2010-1-1'
union all select '基本工资',1200,2,'2010-1-1'
union all select '奖金',1500,2,'2010-1-1'
union all select '补助',600,2,'2010-1-1'
union all select '福利',600,2,'2010-1-1'
go
--代码实现select * from tb
declare @sql varchar(max)
select @sql=isnull(@sql+','+rtrim(fname)+'=max(case when fname='''+rtrim(fname)+''' then fvalue else 0 end)',rtrim(fname)+'=max(case when fname='''+rtrim(fname)+''' then fvalue else 0 end)')
from (select distinct fname from tb)t
print @sql
exec('select FmployeeID,'+@sql+',时间=convert(varchar(10),FDate,120) from tb group by FmployeeID,FDate')/*测试结果FmployeeID 补助 福利 基本工资 奖金 时间
-------------------------------------------
1 500 0 1000 1000 2010-01-01
2 600 600 1200 1500 2010-01-01(1 行受影响)
*/
set @sql = 'select b.fname '
select @sql = @sql + ' , max(case fName when ''' + fName + ''' then fValue end) [' + fName + ']'
+ ' , max(case fName when ''' + fName + ''' then fValue end) [' + fName + ']'
+ ' , max(case fName when ''' + fName + ''' then fValue end) [' + fName + ']'
from (select distinct fName from a) as t
set @sql = @sql + ' from a , b where a.FmployeeID = b.FmployeeID group by b.fname,a.时间'
exec(@sql)