基础数据:
——————————————————————————————
fptid fieldid content gid flag
ezcat billid 123 1 1
ezcat phone 445 1 1
ezcat phone 346 2 1
ezcat billid 567 2 1
——————————————————————————————
要求:可否转成下面様子?
——————————————————————————————
ftpid billid phone gid
ezcat 123 445 1
ezcat 346 567 2
——————————————————————————————
——————————————————————————————
fptid fieldid content gid flag
ezcat billid 123 1 1
ezcat phone 445 1 1
ezcat phone 346 2 1
ezcat billid 567 2 1
——————————————————————————————
要求:可否转成下面様子?
——————————————————————————————
ftpid billid phone gid
ezcat 123 445 1
ezcat 346 567 2
——————————————————————————————
解决方案 »
- 写个触发器?
- 急求一个查询过程的解决办法,小弟对SQL一窍不通,领导急要,谢谢大家啊!
- 求一sql语句
- 求一个sql语句
- 请教有关标量函数(FN), 内嵌表函数(IF),还有表函数(TF)的三者的详细说明
- 在SQL Server 中有一个字段,如何设置其默认值为当前日期?
- sqlserver 2005导出数据报错未安装LOCALEID 2052
- 雪地在线裸跪求教:为什么我用DTS把excel表格导入到数据库中,excel里明明只是一条记录,倒进数据库里却有6条一模一样的记录(解决立马给
- IIF的问题
- 关于MS Sql中表的查询速度问题?高手快进!!
- 求sql
- 求如何操作sql2005,可使其crash,dump的操作
(
fptid varchar(10),
fieldid varchar(10),
content int,
gid int,
flag int
)insert into tb
select 'ezcat','billid',123,1,1 union all
select 'ezcat','phone',445,1,1 union all
select 'ezcat','phone',346,2,1 union all
select 'ezcat','billid',567,2,1select fptid
,sum(case when fieldid='billid' then content else 0 end) as billid
,sum(case when fieldid='phone' then content else 0 end) as phone
,gid
from tb group by fptid,gid
/*
------------------------
fptid billid phone gid
ezcat 123 445 1
ezcat 567 346 2
*/
ftpid,
billid = max(case fieldid when 'billid' then content else 0 end),
phone = max(case fieldid when 'phone' then content else 0 end),
gid
group by ftpid, gid
declare @table table (fptid varchar(5),fieldid varchar(6),content int,gid int,flag int)
insert into @table
select 'ezcat','billid',123,1,1 union all
select 'ezcat','phone',445,1,1 union all
select 'ezcat','phone',346,2,1 union all
select 'ezcat','billid',567,2,1select * from (
select a.fptid,a.[content] as billid ,b.[content] as phone
,a.gid from @table a left join @table b
on a.fptid=b.fptid and a.gid=b.gid
and a.content<b.content
) aa where phone is not null
/*
fptid billid phone gid
----- ----------- ----------- -----------
ezcat 123 445 1
ezcat 346 567 2
*/
——————————————————————————————
fptid fieldid content gid flag
ezcat billid 123 1 1
ezcat phone 551 1 1
ezcat address 上海 1 1
ezcat billid 346 1 1
ezcat phone 789 2 1
ezcat address 北京 1
——————————————————————————————
要求:可否转成下面様子?
——————————————————————————————
ftpid billid phone address gid
ezcat 123 551 上海 1
ezcat 346 789 北京 2
——————————————————————————————
create table tb
(
fptid varchar(10),
fieldid varchar(10),
content nvarchar(10),
gid int,
flag int
)insert into tb
select 'ezcat','billid','123',1,1 union all
select 'ezcat','phone','445',1,1 union all
select 'ezcat','phone','346',2,1 union all
select 'ezcat','billid','567',2,1 union all
select 'ezcat','address','上海',1,1 union all
select 'ezcat','address','北京',2,1
select fptid
,max(case when fieldid='billid' then content end) as billid
,max(case when fieldid='phone' then content end) as phone
,max(case when fieldid='address' then content end) as address
,gid
from tb group by fptid,gid
/*
----------------------
fptid billid phone address gid
ezcat 123 445 上海 1
ezcat 567 346 北京 2
*/
Thanks yours answer,
That's great!!
利害!!
select fptid,billid,phone,gid from t2 pivot(max(content) for fieldid in(billid,phone)) t
</code>
select fptid,billid,phone,gid from t2 pivot(max(content) for fieldid in(billid,phone)) t
上面代码回复格式写错了