sql - Excel Pivot: pivot two columns in table without summarizing values -


i'm using query in excel following table:

select  "work_center"."eqno", "ud_data"."ud_cols_id", "work_center"."cuser1", "work_center"."cuser2", "work_center"."cuser3", "work_center"."cuser4",  "work_center"."cuser5", "work_center"."nuser1", "work_center"."nuser2", "work_center"."nuser3", "work_center"."nuser4", "work_center"."nuser5", "ud_data"."cuser"   "iqms"."ud_data" "ud_data"  full outer join "iqms"."work_center" "work_center"  on  "ud_data"."parent_id"="work_center"."id"    "work_center"."mfg_type"='injection'   ,  "ud_data"."ud_cols_id" not null order "work_center"."eqno"

result

and following table: table

which has duplicates apart ud_cols_id(16,17,16,17...) , cuser(1000,test1,2000,test2.....)

desired result

but i've been trying in excel pivot column ud_cols_id on cuser(the last column in previous image) column, remove duplicate lines , this.

i've tried using excels pivot table can't seem prevent summarizing values e.g have ud_cols_id column headings, eqno , cuser values. keeps summarizing value field , there doesn't seem way not choose that? image

searching fixes keeps leading me examples want summarize data. , adding more values row , columns makes harder follow.

how should go doing this?

you're looking more this:

select    work_center.eqno,   work_center.cuser1,   work_center.cuser2,   work_center.cuser3,   work_center.cuser4,    work_center.cuser5,   work_center.nuser1,   work_center.nuser2,   work_center.nuser3,   work_center.nuser4,   work_center.nuser5,   ud_data_id_16.cuser id_16,   ud_data_id_17.cuser id_17       iqms.work_center work_center    inner join   iqms.ud_data ud_data_id_16     on     work_center.id=ud_data_id_16.parent_id ,     ud_data_id_16.ud_cols_id = 16    inner join   iqms.ud_data ud_data_id_17     on     work_center.id=ud_data_id_17.parent_id ,     ud_data_id_17.ud_cols_id = 17      work_center.mfg_type='injection' order    work_center.eqno

why? well, ud_data has 2 rows per every work_center row, don't want both rows each associating every work_center row because double work_center rows. called cartesian product. gets worse if there x rows in work_center y rows in ud_data, number of duplicates becomes x * y

what need convert multiple rows per "thing" in ud_data single rows. either pivoting data in oracle, or joining table multiple times, , specifying reduces relationship 1:1 between ud_data , work_center.

a work_center row simplified column layout:

id, ud_data_id wc1, ud1 wc2, ud2 wc3, ud3

a ud_data row set:

id, thing, otherthing ud1, apple, 100 ud1, android, 101 ud2, apple, hello ud2, android, goodbye ud3, apple, jan-17 ud3, android, dec-25

the 2 ud rows per work centre row need become 1 row. either before join once, pivoting ud data rows this:

id, applething, androidthing ud1, 100, 101 ud2, hello, goodbye ud3, jan-17, dec25

or join twice, selecting youre interested in each time (not syntactically correct regard id - no db accept this, that's not point being made):

work_center join ud_data on id=id   , ud_data.thing='apple' join ud_data on id=id   , ud_data.thing='android'

Comments

Post a Comment

Popular posts from this blog

networking - Vagrant-provisioned VirtualBox VM is not reachable from Ubuntu host -

this question exact duplicate of: puphpet/vagrant: unable access lamp vm ip address 1 answer i following basic vagrant tutorial , vagrant provisions box on ubuntu without complaining. moreover, can vagrant ssh vm. however, vm not reachable host: it's not possible ping it's ip address. what have achieved: installed virtualbox (5.1.24 r117012 (qt5.5.1)) , vagrant (1.9.7) onto ubuntu (16.04.2 lts) machine. provisioned vm vagrant up , vagrant finishes job , not complain. vagrant ssh works. i have manually installed lighttpd on guest vm , can ping/curl address: localhost within guest vm. this when trying reach guest vm host: ~/tmp/myfirstvagrantproject$ ping 172.22.22.22 ping 172.22.22.22 (172.22.22.22) 56(84) bytes of data. ... icmp_seq=4 destination net unreachable ... 171 packets transmitted, 0 received, +21 errors, 100% packet loss, time 173
Read more

c# - ASP.NET Core - There is already an object named 'AspNetRoles' in the database -

i'm receiving error applicationdbcontext database when trying update entity framework. i've googled, tried fixes here on so, no avail. i'm deleting database each time make change, once deploy, not option. here's error: pm> update-database -context applicationdbcontext system.data.sqlclient.sqlexception: there object named 'aspnetroles' in database. @ system.data.sqlclient.sqlconnection.onerror(sqlexception exception, boolean breakconnection, action 1 wrapcloseinaction) @ system.data.sqlclient.tdsparser.throwexceptionandwarning(tdsparserstateobject stateobj, boolean callerhasconnectionlock, boolean asyncclose) @ system.data.sqlclient.tdsparser.tryrun(runbehavior runbehavior, sqlcommand cmdhandler, sqldatareader datastream, bulkcopysimpleresultset bulkcopyhandler, tdsparserstateobject stateobj, boolean& dataready) @ system.data.sqlclient.sqlcommand.runexecutenonquerytds(string methodname, boolean async, int32 timeout, boolean
Read more

ruby on rails - ArgumentError: Missing host to link to! Please provide the :host parameter, set default_url_options[:host], or set :only_path to true -

i have built module in solidus , ruby on rails , try create tests. i trying create integration tests , have error: e error: prescriptionphotostest#test_/prescription_photos_post_endpoint_throws_an_error_when_wrong_params: argumenterror: missing host link to! please provide :host parameter, set default_url_options[:host], or set :only_path true test/integration/prescription_photos_test.rb:6:in `block in ' i read this , this , doesn't make difference. my code returning error on line: @user = factorygirl.create(:user) here code files. prescription_photos_rest.rb require 'test_helper' class prescriptionphotostest < actiondispatch::integrationtest setup spree::auth::config[:confirmable] = false @user = factorygirl.create(:user) end def authenticated_header token = knock::authtoken.new(payload: { sub: @user.id }).token { 'authorization': "bearer #{token}" } end test '/prescription_p
Read more