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 > "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து" >