sql - Crosstab function in Postgres returning a one row output when I expect multiple rows -


i have table m of following format:

id    scenario    period    ct 2        1          1       1   2        1          2       1 2        1          3       1 2        1          4       1 2        2          1       1 2        2          2       1 2        2          3       1 2        2          4       1 2        3          1       1 2        3          2       1 2        3          3       1 2        3          4       1

i want create following table:

id    scenario    period    1    2    3    4 2        1          1       1 2        1          2            1 2        1          3                 1 2        1          4                      1 2        2          1       1 2        2          2            1 2        2          3                 1 2        2          4                      1 2        3          1       1 2        3          2            1 2        3          3                 1 2        3          4                      1

the tablefunc extension has been created in postgres database already. i'm trying use crosstab() function complete pivot. however, i'm getting table looks following:

id    scenario    period    1    2   3    4  2        1          1       1    1   1    1

the query tried:

select * crosstab(         'select id, scenario, period, ct m              order 1',          'select distinct period m              order 1') (id, scenario, period, 1, 2, 3, 4);

this query generates desired output:

select id, scenario, period, p1, p2, p3, p4  -- except aux column rn   crosstab(   'select row_number() on (order id, scenario, period)::int rn         , id, scenario, period, period, ct      m    order  1' , 'values (1), (2), (3), (4)'    ) (rn int, id int, scenario int, period int, p1 int, p2 int, p3 int, p4 int);

two special difficulties:

  1. you don't have single unique column row_name yet. use row_number() generate surrogate key: rn. removed outer select match desired result.
    way tried it, id taken row_name , input rows aggregated single output row.

  2. you want additional columns (scenario , period) in result, must come after row_name , before category. must list period twice original column additionally - redundant though may seem.

basics:

related particular case:

typically, have query this:

select id, scenario, p1, p2, p3, p4  -- except aux column rn   crosstab(   'select rank() on (order id, scenario)::int rn         , id, scenario, period, ct      m    order  1' , 'values (1), (2), (3), (4)'    ) (rn int, id int, scenario int, p1 int, p2 int, p3 int, p4 int);

with output this:

id   scenario   p1   p2   p3   p4 2    1          1    1    1    1 2    2          1    1    1    1 2    3          1    1    1    1

note use of rank() instead of row_number() group same combinations of (id, scenario) together.
result makes more sense if counts not 1.


Comments

Post a Comment

Popular posts from this blog

resizing Telegram inline keyboard -

telegram's inline keyboard great feature lots of different use cases. inline buttons added list of items this: inline_keyboard = [[inlinekeyboardbutton(text="button", callback_data="button"), inlinekeyboardbutton(text="reset",callback_data="reset")]] inline_keyboard_markup = inlinekeyboardmarkup(inline_keyboard) update.message.reply_text("hi", reply_markup=inline_keyboard_markup) the above code adds 2 buttons each half width of chat screen. i know the normal keyboard button there resize_keyboard parameter somehow can used. my question is there way resize inline buttons ? example make full width or quarter width. unfortunately, can't now. :( you can suggest @botsupport , might add feature next version.
Read more

javascript - How to bind ViewModel Store to View? -

i'm pretty new ext js , trying embed multiselect inside panel . the viewmodel has stores property can see here: ext.define('test.view.controls.search.searchfiltermodel', { extend: 'ext.app.viewmodel', alias: 'viewmodel.filter', data: { title: '' }, stores: { test: { fields: [ 'id', 'name' ], proxy: { type: 'ajax', url: 'api/test', reader: 'array' }, autoload: true } } }); i bind in view this: viewmodel: { type: 'filter' }, layout: 'fit', border: 1, plain: true, scrollable: 'y', layout: 'fit', bind: { title: '{title}', }, items: { xtype: 'multiselect', scrollable: false, allowblank: true, ddreorder: true, bind: { store: '{test}' }, valuefield: 'id', displayfield: 'name' } ...
Read more

c - Why does alarm() cause fgets() to stop waiting? -

i playing around signals in c. main function asks input using fgets(name, 30, stdin) , , sits there , waits. set alarm alarm(3) , , reassigned sigalrm call function myalarm calls system("say pay attention") . after alarm goes off, fgets() stops waiting input , main fn continues on. happens if change myalarm set variable , nothing it. void myalarm(int sig) { //system("say pay attention"); int x = 0; } int catch_signal(int sig, void (*handler)(int)) { // when signal comes in, "catch" , "handle it" in way want struct sigaction action; // create new sigaction action.sa_handler = handler; // set it's sa_handler attribute function specified in header sigemptyset(&action.sa_mask); // "turn signals in sa_mask off?" "set sa_mask contian no signals, i.e. nothing masked?" action.sa_flags = 0; // not sure, looks aren't using of available flags, whatever may return sigaction(sig, ...
Read more