UC知道将一表数据查询形成新字段得新表
来源：百度知道 编辑：UC知道 时间：2024/05/29 07:50:27
tabel
id depart cost type
1 ab 56 information
2 ab 78 admin
3 cd 80 computer
4 cd 77 information
查询后得
name informationcost computercost admincost
ab 56 null 78
cd 77 80 null
也就是说把type字段中不同类型的形成新的字段,然后根据部门得出cost,information,compurter,admin都是固定的,可以用type='information'进行定位.
id depart cost type
1 ab 56 information
2 ab 78 admin
3 cd 80 computer
4 cd 77 information
查询后得
name informationcost computercost admincost
ab 56 null 78
cd 77 80 null
也就是说把type字段中不同类型的形成新的字段,然后根据部门得出cost,information,compurter,admin都是固定的,可以用type='information'进行定位.
tablename=tabel?
代码如下：
select depart as name,sum(case when type='information' then cost else 0 end) as informationcost,sum(case when type='computer' then cost else 0 end) as computercost,sum(case when type='admin' then cost else 0 end) as admincost into NewTablename from tabel group by depart
--> --> (Roy)生成测试数据
if not object_id('tabel') is null
drop table tabel
Go
Create table tabel([id] int,[depart] nvarchar(2),[cost] int,[type] nvarchar(11))
Insert tabel
select 1,N'ab',56,N'information' union all
select 2,N'ab',78,N'admin' union all
select 3,N'cd',80,N'computer' union all
select 4,N'cd',77,N'information'
Go
declare @s nvarchar(4000)
set @s='select [depart]'
Select @s=@s+','+[type]+'=max(case when [type]='''+[type]+''' then [cost] end)' f