款号,颜色,
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, @[email protected]
while @@fetch_status=0
begin
if @[email protected] and @ID%6 <> 0
begin
insert @dt values(@groupID, @ID, @款号, @颜色)
set @[email protected]+1
end
else
begin
select @[email protected]+1, @ID=2, @[email protected]
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)