请教这样的数据集应如何生成?
款号,颜色,
y555201,21
y555201,26
H555201,33
H555201,3A
H555201,39
H555201,34
H555201,88
H555201,31
H555201,3F
H555201,A3


要生成数据集,形如:
款号 ,颜色1,颜色2,颜色3,颜色4,颜色5
y555201,21, 26, NULL, NULL,NULL
H555201,33, 3A, 39, 34, 88
H555201,31, 3F, A3, NULL,NULL
...
也即，每行5个颜色字段，不够的补充NULL,

谢谢




[解决办法]
sql2000得法
1: 列行:
eg1:
Create table test (name char(10),km char(10),cj int)
go
insert test values( '三 ', '文 ',80)
insert test values( '三 ', ' ',86)
insert test values( '三 ', '英 ',75)
insert test values( '李四 ', '文 ',78)
insert test values( '李四 ', ' ',85)
insert test values( '李四 ', '英 ',78)

想成

姓名 文 英
三 80 86 75
李四 78 85 78


declare @sql varchar(8000)
set @sql = 'select name '
select @sql = @sql + ',sum(case km when ' ' '+km+ ' ' ' then cj end) [ '+km+ '] '
from (select distinct km from test) as a
select @sql = @sql+ ' from test group by name '
exec(@sql)

drop table test

2005已有pivot法,就不需要Function那了.

[解决办法]
declare @sql varchar(8000)
set @sql = 'select 款号 '
select @sql = @sql + ',sum(case 颜色 when ' ' '+颜色+ ' ' ' then ' ' '+颜色+ ' ' ' end) [ '+颜色+ '] '
from (select distinct 颜色 from #temp) as a
select @sql = @sql+ ' from #temp group by 款号 '
print @sql
exec(@sql)
[解决办法]
create table T(款号 varchar(20), 颜色 varchar(20))
insert T select 'y555201 ', '21 '
union all select 'y555201 ', '26 '
union all select 'H555201 ', '33 '
union all select 'H555201 ', '3A '
union all select 'H555201 ', '39 '
union all select 'H555201 ', '34 '
union all select 'H555201 ', '88 '
union all select 'H555201 ', '31 '
union all select 'H555201 ', '3F '
union all select 'H555201 ', 'A3 '

declare @groupID int, @ID int, @款号 varchar(20), @颜色 varchar(20), @旧款号 varchar(20)
declare @dt table(groupID int, ID int, 款号 varchar(20), 颜色 varchar(20))

declare cur cursor local
for
select * from T
open cur

fetch next from cur into @款号, @颜色
select @groupID=1, @ID=1, @旧款号=@款号
while @@fetch_status=0
begin
if @旧款号=@款号 and @ID%6 <> 0
begin
insert @dt values(@groupID, @ID, @款号, @颜色)
set @ID=@ID+1
end
else
begin
select @groupID=@groupID+1, @ID=2, @旧款号=@款号
insert @dt values(@groupID, 1, @款号, @颜色)
end

fetch next from cur into @款号, @颜色
end

close cur
deallocate cur

select
款号,
颜色1=max(case when ID=1 then 颜色 end),
颜色2=max(case when ID=2 then 颜色 end),
颜色3=max(case when ID=3 then 颜色 end),
颜色4=max(case when ID=4 then 颜色 end),
颜色5=max(case when ID=5 then 颜色 end)

from @dt
group by groupID, 款号
order by groupID


--result
款号 颜色1 颜色2 颜色3 颜色4 颜色5
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
y555201 21 26 NULL NULL NULL
H555201 33 3A 39 34 88
H555201 31 3F A3 NULL NULL

(3 row(s) affected)
[解决办法]
用交叉数据报表方式实现