现在有张表ID employeeCode employeeName aword pay
1 0001 Allen 2400 2000
2 0002 Betty 3900 2000
3 0003 Kyle 1900 2000
4 0004 Jessi 3200 2000
5 0005 Tom 2300 2000
我现在要返回这么一个结果集 Allen Betty Kyle Jessi Tom
4400 5900 3900 5200 4300
只能返回一行记录 记录里的数据 是aword+pay并且employeeName是不确定的 要可以动态显示 例如原表中多加了一条记录,所返回的结果集中也应该多一列出来。各位大大,应该怎么做?谢谢了!
1 0001 Allen 2400 2000
2 0002 Betty 3900 2000
3 0003 Kyle 1900 2000
4 0004 Jessi 3200 2000
5 0005 Tom 2300 2000
我现在要返回这么一个结果集 Allen Betty Kyle Jessi Tom
4400 5900 3900 5200 4300
只能返回一行记录 记录里的数据 是aword+pay并且employeeName是不确定的 要可以动态显示 例如原表中多加了一条记录,所返回的结果集中也应该多一列出来。各位大大,应该怎么做?谢谢了!
declare @s varchar(8000)
select @s= isnull(@s+',','')+'['+employeename+'] = sum(case when employeename = '''+employeename+''' then aword+pay else 0 end)'
from (select distinct employeename from ta ) a
exec('select '+@s+' from ta ')
-- Author: flystone
-- Version:V1.001
-- Date:2010-03-30 21:10:26
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,employeeCode nvarchar(4),employeeName nvarchar(5),aword int,pay int)
Go
Insert into ta
select 1,'0001','Allen','2400','2000' union all
select 2,'0002','Betty','3900','2000' union all
select 3,'0003','Kyle','1900','2000' union all
select 4,'0004','Jessi','3200','2000' union all
select 5,'0005','Tom','2300','2000'
Go
--Start
declare @s varchar(8000)
select @s= isnull(@s+',','')+'['+employeename+'] = sum(case when employeename = '''+employeename+''' then aword+pay else 0 end)'
from (select distinct employeename from ta ) a
exec('select '+@s+' from ta ')
--Result:
/*
Allen Betty Jessi Kyle Tom
----------- ----------- ----------- ----------- -----------
4400 5900 5200 3900 4300*/
--End
-- Author : htl258(Tony)
-- Date : 2010-03-30 21:08:22
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[employeeCode] NVARCHAR(10),[employeeName] NVARCHAR(10),[aword] INT,[pay] INT)
INSERT [tb]
SELECT 1,'0001','Allen',2400,2000 UNION ALL
SELECT 2,'0002','Betty',3900,2000 UNION ALL
SELECT 3,'0003','Kyle',1900,2000 UNION ALL
SELECT 4,'0004','Jessi',3200,2000 UNION ALL
SELECT 5,'0005','Tom',2300,2000
GO
--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @S VARCHAR(MAX)
SELECT @S=ISNULL(@S+',','')+QUOTENAME([EMPLOYEENAME]) FROM TB GROUP BY [EMPLOYEENAME]
EXEC('
SELECT *
FROM (
SELECT [EMPLOYEENAME],ISNULL([AWORD],0)+ISNULL([PAY],0) AS AWORDPAY
FROM TB
) AS T
PIVOT(MAX(AWORDPAY) FOR [EMPLOYEENAME] IN('+@S+')) P'
)/*
Allen Betty Jessi Kyle Tom
----------- ----------- ----------- ----------- -----------
4400 5900 5200 3900 4300(1 行受影响)
*/
Go
Insert into ta
select 1,'0001','Allen','2400','2000' union all
select 2,'0002','Betty','3900','2000' union all
select 3,'0003','Kyle','1900','2000' union all
select 4,'0004','Jessi','3200','2000' union all
select 5,'0005','Tom','2300','2000'
Go
declare @col as varchar(100)set @col=stuff((select ','+quotename(employeeName) from ta order by ID for xml path('')),1,1,'')
exec ('select '+@col+' from (select employeeName,aword+pay as pay from ta) a pivot (max(pay) for employeeName in('+@col+')) pvt')Allen Betty Kyle Jessi Tom
----------- ----------- ----------- ----------- -----------
4400 5900 3900 5200 4300(1 行受影响)
PIVOT 太生涩难懂