急急急!
ID WarrantNo Kind
1 0086 A
2 0087 B
3 0087 C
问：如何把WarrantNo列相同的数据放在一行上显示?
如何得到这种结果?
ID WarrantNo Kind Kind
1 0086 A NULL
2 0087 B C
问题点数：10、回复次数：6Top
1 楼dulei115（前途无亮）回复于 2006-06-01 13:57:16 得分 0
select min(Id) as Id, WarrantNo, max(case findex when 1 then Kind end) as Kind1,
max(case findex when 2 then Kind end) as Kind2
from (select *,
(select count(1) + 1
from tablename
where WarrantNo = a.WarrantNo) as findex
from tablename a) b
group by WarrantNoTop
2 楼dulei115（前途无亮）回复于 2006-06-01 14:01:07 得分 0
改一下
if object_id('tablename') is not null drop table tablename
select 1 as ID, '0086' as WarrantNo, 'A' as Kind
into tablename
union select 2, '0087', 'B'
union select 3, '0087', 'C'
-----------------------------------
select min(Id) as Id, WarrantNo, max(case findex when 1 then Kind end) as Kind1,
max(case findex when 2 then Kind end) as Kind2
from (select *,
(select count(1) + 1
from tablename
where WarrantNo = a.WarrantNo and Kind < a.Kind) as findex
from tablename a) b
group by WarrantNo
/*
Id WarrantNo Kind1 Kind2
1 0086 A NULL
2 0087 B C
*/
-----------------------------------
drop table tablenameTop
3 楼wgqqgw（小强）回复于 2006-06-01 14:03:08 得分 0
能否确定到底一个WarrantNo最多会有多少个Kind呢？如果无法确定，那么其实很难搞出你想要的效果，我能想到的方法就是先找出最多Kind的WarrantNo，然后根据Kind的数据作临时表，然后交叉表的方式把数据填进去，实在有点费时费力。
其实能不能换一种角度去实现目标呢？重新考虑一下最终的目的是什么试试看吧。Top
4 楼yzgwzhjzhj（yz老八）回复于 2006-06-01 14:14:02 得分 0
ID WarrantNo Kind Kink2
1 0086 A NULL
2 0087 B NULL
3 0087 NULL C
问：如何把WarrantNo列相同的数据放在一行上显示?
如何得到这种结果?
ID不是自动增长列
ID WarrantNo Kind Kind2
1 0086 A NULL
2 0087 B C
Top
5 楼yzgwzhjzhj（yz老八）回复于 2006-06-01 14:14:42 得分 0
WarrantNo 最多6个KindTop
6 楼dulei115（前途无亮）回复于 2006-06-01 15:18:16 得分 0
select min(Id) as Id, WarrantNo, max(case findex when 0 then Kind end) as Kind,
max(case findex when 1 then Kind end) as Kind2,
max(case findex when 2 then Kind end) as Kind3,
max(case findex when 3 then Kind end) as Kind4,
max(case findex when 4 then Kind end) as Kind5,
max(case findex when 5 then Kind end) as Kind6
from (select *,
(select count(1)
from tablename
where WarrantNo = a.WarrantNo and Kind < a.Kind) as findex
from tablename a) b
group by WarrantNoTop
