oracle11g - How to create a concatenated column if your are getting data from a single column? -

-



i have column called issue_status column, presents current status of issue raised related in risk management. need to create new column in both cognos and/or sql server present concatenated columns of previous statuses of issue. goes this.

right now, don't have issue_status_concatenated.

it goes this.

issue i.d   issue status     1234        draft            1234        open            1234        closed           5678        draft            5678        pending          5678        closed           5678        cancelled        3333        draft            3333        pending          3333        pending          3333        pending

i thinking of using running count function (cognos function)

issue i.d   issue status         running_count  1234        draft                1 1234        open                 2 1234        closed               3 5678        draft                1 5678        pending              2 5678        closed               3 5678        cancelled            4 3333        draft                1 3333        pending              2 3333        pending              3 3333        pending              4

then combine results inside single column using case function

new concat_column 1  case when running_count = 1 issue_status else ' ' end  new concat_column 2   case when running_count = 2 issue_status else ' ' end  new concat_column 3   case when running_count = 3 issue_status else ' ' end  new concat_column 4  case when running_count = 4 issue_status else ' ' end

then plan create new data item or new column creating issue_status_concatenated combining new concat columns 1 

new concat_column 1 + '|' + new concat_column  2 + '|' + new concat_column 3 + '|' + new concat_column 4

i know long process know there easier , logical way this? there way make simpler?

issue i.d   issue status   issue_status_concatenated      1234        draft           draft | open | closed     1234        open            draft | open | closed     1234        closed          draft | open | closed     5678        draft           draft | open | closed |cancelled     5678        pending         draft | open | closed |cancelled     5678        closed          draft | open | closed |cancelled     5678        cancelled       draft | open | closed |cancelled     3333        draft           draft | pending     3333        pending         draft | pending     3333        pending         draft | pending     3333        pending         draft | pending

below snippet desird output in oracle. hope helps.

select id,   stat,   listagg(stat,'|') within group( order stat) over(partition id) agg_stat   (with tmp   ( select 1234 id, 'draft' stat dual   union   select 1234 id, 'open' stat dual   union   select 1234 id, 'completed' stat dual   union   select 1100 id, 'draft' stat dual   union   select 1100 id, 'pending' stat dual   union   select 1100 id, 'completed' stat dual   union   select 1100 id, 'closed' stat dual   ) select tmp.*,row_number() over(partition id order 1 desc) rn tmp   );




wiki

Comments

Post a Comment

Popular posts from this blog

python - Read npy file directly from S3 StreamingBody -

-
i have many .npy (numpy) , .json files saved in s3, need download , load these files. for json files downloading files , loading dictionary using following code: obj = s3.object(bucket, key) response = obj.get() body = response['body'].read().decode('utf-8') data = json.loads(body) that way avoid writing .json file disk , reading it. now want same .npy files, equivalent? currently downloading file, saving temp file on disk , loading np.load avoid if possible. following https://stackoverflow.com/a/28196540/3509999 : obj = s3.object(bucket, key) io.bytesio(obj.get()["body"].read()) f: # rewind file f.seek(0) arr = np.load(f) wiki
Read more

kotlin - Out-projected type in generic interface prohibits the use of metod with generic parameter -

-
i start using kotlin , defined interface this: interface aadapter<vh : recyclerview.viewholder> { fun oncreateaviewholder(parent: viewgroup): vh fun onbindaviewholder(v: vh, position: int) } and when try use on code: class klasa ( private val adapter: aadapter<*> ) { fun dosth(){ //... val vh = this.adapter.oncreateaviewholder(parent) //on below line error adapter.onbindaviewholder(v, position) //... } } i error out-projected type 'aadapter<*>' prohibits use of 'public abstract fun onbindaviewholder(v: t, position: int): unit i tied add "in" or "out" definition i'm confused. how allow it. replace aadapter<*> aadapter<recyclerview.viewholder> . class klasa ( private val adapter: aadapter<recyclerview.viewholder> ) { // ... because have declared aadapter has type parameter vh type subclass of recyclerview....
Read more

Asterisk AGI Python Script to Dialplan does not work -

-
i'm stuck project have check database of sip extension incoming caller id , dial sip extension. below receive when run script on asterisk cli. -- registered sip '9125' @ 192.168.1.102:50142 > saved useragent "zoiper rv2.8.40" peer 9125 [2017-08-22 13:53:33] notice[5095]: chan_sip.c:24558 handle_response_peerpoke: peer '9125' reachable. (17ms / 2000ms) == using sip rtp tos bits 184 == using sip rtp cos mark 5 -- executing [0112617769@from-trunk:1] answer("sip/obitrunk1-0000000d", "") in new stack > 0x1ca50d0 -- probation passed - setting rtp source address 192.168.1.98:16834 -- executing [0112617769@from-trunk:2] agi("sip/obitrunk1-0000000d", "test.py") in new stack -- launched agi script /var/lib/asterisk/agi-bin/test.py args: ['/var/lib/asterisk/agi-bin/test.py'] env line: agi_request: test.py env line: agi_channel: sip/obitrunk1-0000000d env line: agi_language: en env line: a...
Read more