sql - PIVOT Oracle - transform multiple row data to single row with multiple columns, no aggregate data -

-

i have need transfer following data set:

enter image description here

in highlighted lines 1 of interest (tag in ('ln','sn')) interested in serialnumber , lotnumber of products. convert data set above following data set: enter image description here

where lists product along serialnumber , lot number in 1 row.

after doing reading online, think pivot might need. however, struggling technical side of statement.

i have tried:

select * ( select * test2 tag in ('ln','sn') ) pivot (   max(value)   tag in ('ln','sn') ) order category,subcat,item,"date"

but not generate output wanted. suggestion? pivot correct statement use or there other statement more appropriate in case? realize pivot requires aggregate function not count or add anything. please advise.

below test table data

  create table "test2"     (    "date" date,      "subcat" varchar2(6 byte),      "category" varchar2(7 byte),      "value" varchar2(17 byte),      "item" varchar2(2 byte),      "description" varchar2(15 byte),      "tag" varchar2(3 byte)    )    insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'25','other attribute','oa'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'25','some comments','sc'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576','1105618','25','lot number','ln'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576','12','25','not relevant','nr'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576','45','25','not use','nu'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576','-1','25','do not care','dc'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576','3x12mm','25','serial number','sn'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'25','abc','abc'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'25','whatever','def'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'28','some attribute','sa'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'28','other attribute','oa'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576','isq: 75, 80','28','other comments','oc'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'28','some comments','sc'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576','1303757','28','lot number','ln'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576','12','28','not relevant','nr'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576','40','28','not use','nu'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576','0','28','do not care','dc'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'28','abc','abc'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'28','whatever','def'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('18-jun-15','dd-mon-rr'),'354506','1219576',null,'4','some attribute','sa'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('18-jun-15','dd-mon-rr'),'354506','1219576',null,'4','other attribute','oa'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('18-jun-15','dd-mon-rr'),'354506','1219576','1403114','4','lot number','ln'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('18-jun-15','dd-mon-rr'),'354506','1219576','11','4','not relevant','nr'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('18-jun-15','dd-mon-rr'),'354506','1219576','50','4','not use','nu'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('18-jun-15','dd-mon-rr'),'354506','1219576','0','4','do not care','dc'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('18-jun-15','dd-mon-rr'),'354506','1219576',null,'4','whatever','def'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('18-jun-15','dd-mon-rr'),'354506','1219576','7777777777','9','lot number','ln'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('18-jun-15','dd-mon-rr'),'354506','1219576','9.999999999999e12','9','serial number','sn');

you aren't doing description, varies tag. isn't aggregated in implicit 'group by', separate rows in result set.

you can either capture (dummy) aggregate:

select * (   select * test2 tag in ('ln', 'sn') ) pivot (   max(value) value, max(description) description   tag in ('ln' ln, 'sn' sn) ) order category, subcat, item, "date";  date      subcat categor ln_value          ln_description  sn_value          sn_description --------- ------ ------- -- ----------------- --------------- ----------------- --------------- 24-oct-13 290223 1219576 25 1105618           lot number      3x12mm            serial number   24-oct-13 290223 1219576 28 1303757           lot number                                        18-jun-15 354506 1219576 4  1403114           lot number                                        18-jun-15 354506 1219576 9  7777777777        lot number      9.999999999999e12 serial number

or more exclude intermediate result set if don't want it, specify columns want instead of using *:

select * (   select category, subcat, item, "date", tag, value   test2 tag in ('ln', 'sn') ) pivot (   max(value) tag in ('ln' ln, 'sn' sn) ) order category, subcat, item, "date";  categor subcat date      ln                sn               ------- ------ -- --------- ----------------- ----------------- 1219576 290223 25 24-oct-13 1105618           3x12mm            1219576 290223 28 24-oct-13 1303757                             1219576 354506 4  18-jun-15 1403114                             1219576 354506 9  18-jun-15 7777777777        9.999999999999e12

Comments

Post a Comment

Popular posts from this blog

PHP DOM loadHTML() method unusual warning -

-
i had following code works fine on localhost: $document = new domdocument(); $document->loadhtml($p_result); $form = $document->getelementsbytagname('form')->item(0); // code continues using $form variable after same code updated on outside server, loadhtml() failed , issued warning. warning: domdocument::loadhtml() expects parameter 1 valid path, string given in path/name/to/script.php returned null instead of object code pretty gets fatal error. note contents of $p_result absolutely same on outside server , on localhost. but why displays kind of warning , why not work? doesn't loadhtml() expects argument 1 string in first place? why method expects parameter 1 valid path ? just make clear i'm not calling loadhtmlfile() , i'm calling loadhtml() . thanks. you're affected one of php bugs . issue present in php 5.6.8 , 5.6.9. have affected php version on server, , bug-free version on localhost. the bug forbids null
Read more

python - How to create jsonb index using GIN on SQLAlchemy? -

-
here's current code creating index jsonb. index("mytable_data_idx_id_key", mytable.data['id'].astext, postgresql_using='gin') but got error. sqlalchemy.exc.programmingerror: (psycopg2.programmingerror) data type text has no default operator class access method "gin" hint: must specify operator class index or define default operator class data type. [sql: "create index event_data_idx_id_key on event using gin ((data ->> 'id'))"] is there way create index on sqlalchemy? the postgresql specific sqlalchemy docs @ http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention postgresql_ops dictionary provide "operator class" used postgresql, , provide example illustrating use: index('my_index', my_table.c.id, my_table.c.data, postgresql_ops={ 'data': 'text_pattern_ops',
Read more

c# - TransactionScope not rolling back although no complete() is called -

-
i'm using transactionscope rollback transaction fail bool errorreported = false; action<importerrorlog> newerrorcallback = e => { errorreported = true; errorcallback(e); }; using (var transaction = new transactionscope()) { foreach (importtaskdefinition task in taskdefinition) { loader.load(streamfile, newerrorcallback, task.destinationtable, processingtaskid); } if (!errorreported) transaction.complete(); } i'm sure there no transactionscope started ahead or after code. i'm using entity framework insert in db. regardless state of errorreported transaction never rolled in case of error. what missing ? transactionscope sets transaction.current . that's does. wants transacted must @ property. i believe ef each time connection opened reason. probably, connection open when scope installed. open connection inside of scope or enlist manually. ef has nasty "design decision": default opens
Read more