4,816
社区成员
发帖
与我相关
我的任务
分享
create table [T] ([name] varchar(4),[class] varchar(4),[money] int)
insert into [T]
select '张三','工资',100 union all
select '张三','奖金',100 union all
select '张三','津贴',100 union all
select '张三','扣款',100 union all
select '李四','工资',100 union all
select '李四','奖金',100 union all
select '李四','津贴',100 union all
select '李四','扣款',100 union all
select '王五','工资',100 union all
select '王五','奖金',100 union all
select '王五','津贴',100 union all
select '王五','扣款',100 union all
select '王五','工资',100
/*
创建带序号的列表,注意,需SQL2005及以上
*/
create view v_t
as
select ROW_NUMBER() over(order by a.name asc) as 'idx',a.name as name1 from
(select distinct name from t) as a
/*
报表用语句
*/
select t.*,v_t.idx from t,v_t
where t.name=v_t.name1
/*
name class money idx
李四 工资 100 1
李四 奖金 100 1
李四 津贴 100 1
李四 扣款 100 1
王五 工资 100 2
王五 奖金 100 2
王五 津贴 100 2
王五 扣款 100 2
王五 工资 100 2
张三 工资 100 3
张三 奖金 100 3
张三 津贴 100 3
张三 扣款 100 3
*/
--> Test Data: [T]
if object_id('[T]') is not null drop table [T]
create table [T] ([name] varchar(4),[class] varchar(4),[money] int)
insert into [T]
select '张三','工资',100 union all
select '张三','奖金',100 union all
select '张三','津贴',100 union all
select '张三','扣款',100 union all
select '李四','工资',100 union all
select '李四','奖金',100 union all
select '李四','津贴',100 union all
select '李四','扣款',100 union all
select '王五','工资',100 union all
select '王五','奖金',100 union all
select '王五','津贴',100 union all
select '王五','扣款',100 union all
select '王五','工资',100
--select * from [T]
--Code
--2000静态
select
[类别]=[class],
[张三]=max(case when [name]='张三' then [money] else 0 end),
[李四]=max(case when [name]='李四' then [money] else 0 end),
[王五]=max(case when [name]='王五' then [money] else 0 end)
from
T
group by [class]
--2000动态
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([name])+'=max(case when [name]='+quotename([name],'''')+' then [money] else 0 end)'
from T group by [name]
exec('select [类别]=[class]'+@s+' from T group by [class]')
--Drop
--Result
/*
类别 张三 李四 王五
---- ----------- ----------- -----------
工资 100 100 100
奖金 100 100 100
津贴 100 100 100
扣款 100 100 100
*/