Como usar o Crosstab no Postgre
02/06/2017
0
Thiago
Post mais votado
02/06/2017
http://www.vertabelo.com/blog/technical-articles/creating-pivot-tables-in-postgresql-using-the-crosstab-function
Fabio Parreira
Mais Posts
04/06/2017
Thiago
04/06/2017
Thiago
http://www.vertabelo.com/blog/technical-articles/creating-pivot-tables-in-postgresql-using-the-crosstab-function
tentei esse código, esta correto?
create or replace FUNCTION testeDinamico_crosTab ()
returns character varying(500) As
$$
DECLARE MAXCount int;
begin
SELECT MaxCount = max(Id)
FROM (
SELECT nome
,count(Id) AS Id
FROM dbo.produtos
GROUP BY nome
) X;
DECLARE SQL character varying (500);
d INT;
d:=0;
WHILE d < @MaxCount
BEGIN
SET d := d + 1;
SET SQL := COALESCE(Sql + ', ', '') + 'Col' + cast(d AS character varying(10));
END
SET SQL := N';WITH CTE AS (
SELECT p.ID, p.nome, ''Col'' + CAST(row_number() OVER (PARTITION BY ID ORDER BY nome) AS character varying(10)) AS RowNo
FROM dbo.produtos p
)
SELECT *
FROM CTE
PIVOT (MAX(p.nome) FOR RowNo IN (' + SQL + N')) pvt';
PRINT SQL;
EXECUTE (SQL);
return SQL;
end;
$$
LANGUAGE plpgsql;
Clique aqui para fazer login e interagir na Comunidade :)