I found it. We can use pivot which is similar to cross tab
In postgres.
Thank you.
On Oct 17, 2016 10:00 PM, "Selvam Raman" <sel...@gmail.com> wrote:
> Hi,
>
> Please share me some idea if you work on this earlier.
> How can i develop postgres CROSSTAB function in spark.
>
> Postgres Example
>
> Example 1:
>
> SELECT mthreport.*
> FROM
> *crosstab*('SELECT i.item_name::text As row_name,
> to_char(if.action_date, ''mon'')::text As bucket,
> SUM(if.num_used)::integer As bucketvalue
> FROM inventory As i INNER JOIN inventory_flow As if
> ON i.item_id = if.item_id
> AND action_date BETWEEN date ''2007-01-01'' and date ''2007-12-31
> 23:59''
> GROUP BY i.item_name, to_char(if.action_date, ''mon''),
> date_part(''month'', if.action_date)
> ORDER BY i.item_name',
> 'SELECT to_char(date ''2007-01-01'' + (n || '' month'')::interval,
> ''mon'') As short_mname
> FROM generate_series(0,11) n')
> As mthreport(item_name text, jan integer, feb integer, mar
> integer,
> apr integer, may integer, jun integer, jul integer,
> aug integer, sep integer, oct integer, nov integer,
> dec integer)
>
> The output of the above crosstab looks as follows:
> [image: crosstab source_sql cat_sql example]
>
> Example 2:
>
> CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
> INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
> INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
> INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
> INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
> INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
> INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
> INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
> INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');
>
> SELECT *
> FROM crosstab(
> 'select rowid, attribute, value
> from ct
> where attribute = ''att2'' or attribute = ''att3''
> order by 1,2')
> AS ct(row_name text, category_1 text, category_2 text, category_3 text);
>
> row_name | category_1 | category_2 | category_3
> ----------+------------+------------+------------
> test1 | val2 | val3 |
> test2 | val6 | val7 |
>
>
> --
> Selvam Raman
> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>
