3

Hello I have created a view, but want to pivot it.

OUTPUT before pivoting:

   tag1  |  qmonth1  |  qmonth2  |  sum1
 --------+-----------+-----------+--------
 name1   |  18-05   |  MAY      |  -166
 name2   |  18-05   |  MAY      |  -86
 name3   |  18-05   |  MAY      |  35
 name1   |  18-06   |  JUN      |  -102
 name2   |  18-06   |  JUN      |  -32
 name3   |  18-06   |  JUN      |  -75
 name1   |  18-09   |  AVG      |  -135
 name2   |  18-09   |  AVG      |  -52
 name3   |  18-09   |  AVG      |  -17

expected output:

 qmonth2 | name1 | name2 | name3
 --------+-------+-------+-------
  MAY    | -166  | -86  |  35
  JUN    | -102  | -32  | -75
  AVG    | -135  | -52  | -17

my full query:

SELECT tag1,qmonth2,sum1 FROM crosstab 
('SELECT tag1::text,qmonth1,qmonth2::text,sum1::numeric 
FROM public."chartdata_chart3"') 
AS ct ( "tag1" TEXT,"qmonth2" TEXT,"sum1" NUMERIC);

I getting this error and unable to resolve:

ERROR:  invalid source data SQL statement
DETAIL:  The provided SQL must return 3 columns: rowid, category, and values.
SQL state: 22023
michal
  • 327
  • 4
  • 15

1 Answers1

2

SQL statement passed as paremeter to crosstab() function must return one row_name column, one category column, and one value column. This in your case is qmonth2, tag1 and sum1.

This is your query considering sum1 is an integer, qmonth2 and tag1 are text:

select *
from crosstab(
  'select qmonth2, tag1, sum1
  from public."chartdata_chart3"
  ') AS ct(qmonth2 text, name1 int, name2 int, name3 int;

Output:

 qmonth2 | name1 | name2 | name3
---------+-------+-------+-------
 MAY     | -166  | -86   | 35
 JUN     | -102  | -32   | -75
 AVG     | -135  | -52   | -17

Refer to manual for more information and samples on how crosstab works.

Also, keep in mind that naming your columns/tables with quotes makes it more complex to write every SQL query so you are better off without doing that :)

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
  • it did worked, but had to changed INT to NUMERIC, as they are negative values, Also is it possible to make the NAME1 & NAME2 & NAME3 column headers automatic, so i don't need to change them if i select different data for TAG1?? – michal Sep 03 '18 at 14:14
  • @michal integers also cover negative values. Use numeric type to handle numbers with fractional parts. If you want to have dynamic headers there's a way using `crosstab(text, text)` function but if the number of columns might change then you would need a dynamic SQL (more complex) to cover the example. – Kamil Gosciminski Sep 03 '18 at 14:19