id 主表id bugid (varchar)
1 E4AB44D4572DB4469043A520F5583491 1(代表1号缺陷)
2 E4AB44D4572DB4469043A520F5583491 2(代表2号缺陷)
3 E4AB44D4572DB4469043A520F5583491 3(代表3号缺陷)
4 E4AB44D4572DB4469043A520F5583492 3(代表3号缺陷) 互换后:
id 主表id bug1 bug2 bug31 E4AB44D4572DB4469043A520F5583491 有 有 有
2 E4AB44D4572DB4469043A520F5583492 无 无 有
1 E4AB44D4572DB4469043A520F5583491 1(代表1号缺陷)
2 E4AB44D4572DB4469043A520F5583491 2(代表2号缺陷)
3 E4AB44D4572DB4469043A520F5583491 3(代表3号缺陷)
4 E4AB44D4572DB4469043A520F5583492 3(代表3号缺陷) 互换后:
id 主表id bug1 bug2 bug31 E4AB44D4572DB4469043A520F5583491 有 有 有
2 E4AB44D4572DB4469043A520F5583492 无 无 有
解决方案 »
- 求组
- sql server如何写带参数的游标?
- 将一个列改为计算列,结果sql server会新建一个临时表,导出,然后删除原来的表
- 数据列汇总问题请教
- 刚刚接触sqlserver,问一下如何在控制面板里配置连接本地的数据库阿?
- 如何取得表中字段的说明,select * from syscolumns where id = object_id('XT_JCDW')
- sql表关联,多条记录合并为一条记录问题,求救!!
- sqlserver2000存储过程能否直接调用WEB程序
- select * from a in 的问题
- 求group by 后满足求差条件后的最小值对应的SQL
- sql server2000如何将数字转换为日期时间
- trafficobject管理sqlserver问题
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html?33238参考
case when bugid =1 then '有' end bug1,
case when bugid =2 then '有' end bug2,
case when bugid =3 then '有' end bug3
from tb group by 主表id
主表id,
case bugid when 1 then '有' end bug1,
case bugid when 2 then '有' end bug2,
case bugid when 3 then '有' end bug3
from
tb
group by
主表id
-- Author: T.O.P
-- Create date: 2009/11/25
-- Version: SQL SERVER 2005
-- =============================================
declare @tb table([id] int,[pid] varchar(32),[bugid] int)
insert @tb
select 1,'E4AB44D4572DB4469043A520F5583491',1 union all
select 2,'E4AB44D4572DB4469043A520F5583491',2 union all
select 3,'E4AB44D4572DB4469043A520F5583491',3 union all
select 4,'E4AB44D4572DB4469043A520F5583492',3select [pid], max(case when [bugid]=1 then '有' ELSE '无' end) as bug1
, max(case when [bugid]=2 then '有' ELSE '无' end) as bug2
, max(case when [bugid]=3 then '有' ELSE '无' end) as bug3
from @tb
group by [pid]
--测试结果:
/*
pid bug1 bug2 bug3
-------------------------------- ---- ---- ----
E4AB44D4572DB4469043A520F5583491 有 有 有
E4AB44D4572DB4469043A520F5583492 无 无 有(2 row(s) affected)
*/
-- Author: T.O.P
-- Create date: 2009/11/25
-- Version: SQL SERVER 2005
-- =============================================
declare @tb table([id] int,[pid] varchar(32),[bugid] int)
insert @tb
select 1,'E4AB44D4572DB4469043A520F5583491',1 union all
select 2,'E4AB44D4572DB4469043A520F5583491',2 union all
select 3,'E4AB44D4572DB4469043A520F5583491',3 union all
select 4,'E4AB44D4572DB4469043A520F5583492',3select (select count(distinct pid) from @tb where t.pid>=pid) as ID,
[pid], max(case when [bugid]=1 then '有' ELSE '无' end) as bug1
, max(case when [bugid]=2 then '有' ELSE '无' end) as bug2
, max(case when [bugid]=3 then '有' ELSE '无' end) as bug3
from @tb t
group by [pid]
--测试结果:
/*
ID pid bug1 bug2 bug3
----------- -------------------------------- ---- ---- ----
1 E4AB44D4572DB4469043A520F5583491 有 有 有
2 E4AB44D4572DB4469043A520F5583492 无 无 有(2 row(s) affected)
*/