我原来用的是SQL Server,现在想试试迁移到Postgresql,就下面这个例子来说,要怎么改?create table tb(姓名 varchar(10) , 课程 int , 分数 int)
insert into tb values('张三' , 1 , 74)
insert into tb values('张三' , 2 , 83)
insert into tb values('张三' , 3 , 93)
insert into tb values('李四' , 1 , 74)
insert into tb values('李四' , 2 , 84)
insert into tb values('李四' , 3 , 94)
go
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + ltrim(课程) from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')姓名 1 2 3
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93(2 行受影响)drop table tb
insert into tb values('张三' , 1 , 74)
insert into tb values('张三' , 2 , 83)
insert into tb values('张三' , 3 , 93)
insert into tb values('李四' , 1 , 74)
insert into tb values('李四' , 2 , 84)
insert into tb values('李四' , 3 , 94)
go
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + ltrim(课程) from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')姓名 1 2 3
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93(2 行受影响)drop table tb
REATE FUNCTION dt1() RETURNS SETOF record AS $$
declare r record;
ff varchar:='SELECT "A",';
begin
for r in select distinct "B" from tth1 loop
ff:=ff || 'max(case when "B"='''||r."B" || '''' || ' then "C" ' || ' end ),';
RAISE NOTICE '123(%)', ff;
end loop;
ff:=substr(ff,1,length(ff)-1) || ' from tth1 group by "A"';
FOR r IN execute ff Loop
RETURN NEXT r;
END LOOP;
end;
$$ LANGUAGE plpgsql;select * from dt1() a(q text,d float,e float,f float);a:姓名 , b:课程 , c:分数