sql - MySQL - Combining Records into Fields -

-

what i'm trying query table holds custom data person , fields, i'm getting each individual field record in return.

the current statement i'm using is

select s.fname, s.lname, s.email, s.mobile, s.country, cf.name, ca.value standard s inner join people p on (s.pid = p.pid) inner join custom_answers ca on (ca.pid = p.pid) inner join custom_fields cf on (cf.fieldid = ca.fieldid) p.acctid = 'xxxxxxxxxx'

this given resultset of 22,000 rows, whereas looking 900 rows.

an example of data output is

fname | lname | email | mobile | country | name     | value tom   | smith | t@t   | 0412   | au      | state    | vic tom   | smith | t@t   | 0412   | au      | position | dept head tom   | smith | t@t   | 0412   | au      | guest    | john smith mick  | jones | m@j   | 0411   | au      | postnom  | aoc mick  | jones | m@j   | 0411   | au      | state    | nsw mick  | jones | m@j   | 0411   | au      | postcode | 2000

whereas output is

fname | lname | email | mobile | country | state | position  | guest      | postnom | postcode tom   | smith | t@t   | 0412   | au      | vic   | dept head | john smith | null    | null mick  | jones | m@j   | 0411   | au      | nsw   | null      | null       | aoc     | 2000

what may or may not cause complication amount of custom fields isn't same each person. may have handful or custom fields, may have upwards of 30.

you'll have apply group by, non-aggregates, , create roll-up... applying max() based on comparing "cf.name" value each element, you'll value per "as" column... if no such record found, remain blank (i've left empty spaces, null...

select        s.fname,        s.lname,        s.email,        s.mobile,        s.country,        max( if( cf.name = 'state', ca.value, '          ' )) state,        max( if( cf.name = 'position', ca.value, '          ' )) position,        max( if( cf.name = 'guest', ca.value, '          ' )) guest,        max( if( cf.name = 'postnom', ca.value, '          ' )) postnom,        max( if( cf.name = 'postcode', ca.value, '          ' )) postcode          standard s          inner join people p              on s.pid = p.pid             inner join custom_answers ca                on p.pid = ca.pid                 inner join custom_fields cf                   on ca.fieldid = cf1.fieldid            p.acctid = 'xxxxxxxxxx'    group       s.fname,       s.lname,       s.email,       s.mobile,       s.country

Comments

Post a Comment

Popular posts from this blog

c# - SVN Error : "svnadmin: E205000: Too many arguments" -

-
i trying reposotries using c# code process svncommand = null; var psi = new processstartinfo("svnadmin"); psi.redirectstandardoutput = true; psi.redirectstandarderror = true; psi.useshellexecute = false; psi.arguments= @"dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump"; using (svncommand = process.start(psi)) { var myoutput = svncommand.standardoutput; var myerror = svncommand.standarderror; debug.write("output :" + environment.newline + environment.newline + myoutput.readtoend() + environment.newline + "error :" + environment.newline + environment.newline + myerror.readtoend()); svncommand.close(); } when use dump command commandline svnadmin dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump it works fine when try ...
Read more

c# - Copy ObservableCollection to another ObservableCollection -

-
how copy observablecollection item observablecollection without reference of first collection? here observablecollection item value changes affecting both collections. code private observablecollection<ratemodel> _allmetalrate = new observablecollection<ratemodel>(); private observablecollection<ratemodel> _metalrateondate = new observablecollection<ratemodel>(); public observablecollection<ratemodel> allmetalrate { { return this._allmetalrate; } set { this._allmetalrate = value; notifypropertychanged("metalrate"); } } public observablecollection<ratemodel> metalrateondate { { return this._metalrateondate; } set { this._metalrateondate = value; notifypropertychanged("metalrateondate"); } } foreach (var item in metalrateondate) allmetalrate.add(item); what causing , how can solve it? you need clone object referenced item before it's a...
Read more

All overlapping substrings matching a java regex -

-
is there api method returns (possibly overlapping) substrings match regular expression? for example, have text string: string t = 04/31 412-555-1235; , , have pattern: pattern p = new pattern("\\d\\d+"); matches strings of 2 or more characters. the matches are: 04, 31, 412, 555, 1235. how overlapping matches? i want code return: 04, 31, 41, 412, 12, 55, 555, 55, 12, 123, 1235, 23, 235, 35. theoretically should possible -- there obvious o(n^2) algorithm enumerates , checks substrings against pattern. edit rather enumerating substrings, safer use region(int start, int end) method in matcher . checking pattern against separate, extracted substring might change result of match (e.g. if there non-capturing group or word boundary check @ start/end of pattern). edit 2 actually, it's unclear whether region() expect zero-width matches. specification vague, , experiments yield disappointing results. for example: string line = "xx90xx"; strin...
Read more