22,209
社区成员
发帖
与我相关
我的任务
分享
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+a from test
select left(@sql,len(@sql)-1)
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB
(
a varchar(5)
)
INSERT INTO TB
select 'a' union all
select 'b' union all
select '3' union all
select 'c' union all
select '5' union all
select '6'
declare @sql varchar(1000)
select @sql=isnull(@sql+',','')+' max(case when a='''+a+''' then a end) a' from tb
set @sql='select '+@sql+' from tb'
exec (@sql)
---------------------
a a a a a a
a b 3 c 5 6
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO
---->建表
create table [TB]([月份] int,[姓名] varchar(2),[类型] varchar(6),[天数] int)
insert [TB]
select 201008,'AA','病假',1 union all
select 201008,'AA','事假',2 union all
select 201008,'AA','有薪假',1 union all
select 201008,'BB','病假',2 union all
select 201008,'BB','事假',1 union all
select 201008,'BB','有薪假',2 union all
select 201009,'AA','病假',1 union all
select 201009,'AA','事假',2 union all
select 201009,'AA','有薪假',1 union all
select 201009,'BB','病假',2 union all
select 201009,'BB','事假',1 union all
select 201009,'BB','有薪假',2
GO
declare @sql varchar(8000)
select @sql = isnull(@sql+',','')+'max(case when 月份='''+ltrim(月份)+''' and 类型= '''+ltrim(类型)+''' then 天数 else null end) as ['+ltrim(月份)+ltrim(类型)+']'
from (
select distinct 月份,类型 from TB t
) r
select @sql = '
select 姓名,'+@sql+'
from TB r
group by 姓名'
exec(@sql)
自己参照改改吧。