parsing - SQL Split Multiple Multivalue Columns into Rows -


i have data sent me, , need normalize it. data in sql table, each row has multiple multi value columns. example following:

id  fname   lname       projects           projdates 1   john    doe         proja;projb;projc  20150701;20150801;20150901 2   jane    smith       projd;;projc       20150701;;20150902 3   lisa    anderson    projb;projc        20150801;20150903 4   nancy   johnson     projb;projc;proje  20150601;20150822;20150904 5   chris   edwards     proja              20150905

needs this:

id  fname   lname      projects projdates 1   john    doe          proja  20150701 1   john    doe          projb  20150801 1   john    doe          projc  20150901 2   jane    smith        projd  20150701 2   jane    smith        projc  20150902 3   lisa    anderson     projb  20150801 3   lisa    anderson     projc  20150903 4   nancy   johnson      projb  20150601 4   nancy   johnson      projc  20150822 4   nancy   johnson      proje  20150904 5   chris   edwards      proja  20150905

i need split rows id, fname, lname, , parsing projects , proddates separate records. have found many posts split functions , can work 1 column, not 2. when 2 columns permeates split. ie john doe, gives me records proja 3 times, once each of proddates. need coorelate each multivalue project record it's respective projdate , not others.

any thoughts?

thanks!

if use jeff moden's "delimitedsplit8k" splitter (which have renamed here "fdelimitedsplit8k") (ref. figure 21: the final "new" splitter code, ready testing)
heavy lifting splits, rest becomes straightforward, using cross apply , proper joining.

if object_id (n'tempdb..#tinputdata') not null     drop table #tinputdata  create table #tinputdata (      id        int          primary key clustered  -- add identity if id needs set @ insert time    , fname     varchar (30)    , lname     varchar (30)    , projects  varchar (4000)    , projdates varchar (4000) )  insert #tinputdata          ( id, fname, lname, projects, projdates ) values    ( 1, 'john',  'doe'      , 'proja;projb;projc' , '20150701;20150801;20150901'),    ( 2, 'jane',  'smith'    , 'projd;;projc'      , '20150701;;20150902'),    ( 3, 'lisa',  'anderson' , 'projb;projc'       , '20150801;20150903'),    ( 4, 'nancy', 'johnson'  , 'projb;projc;proje' , '20150601;20150822;20150904'),    ( 5, 'chris', 'edwards'  , 'proja'             , '20150905')  select * #tinputdata  -- take @ insert results  ; resultset   (    select          indata.id       , indata.fname       , indata.lname       , projectlist.itemnumber projectid       , projectlist.item project       , datelist.itemnumber dateid       , datelist.item projdate    #tinputdata indata    cross apply dbo.fdelimitedsplit8k(indata.projects,';') projectlist    cross apply dbo.fdelimitedsplit8k(indata.projdates,';') datelist    datelist.itemnumber = projectlist.itemnumber  -- links projects , dates in left-to-r1ght order    , (projectlist.item <> '' , datelist.item <> '') -- ignore input lines when both projects , projdates have no value; note these aren't nulls. ) select        id     , fname     , lname     , project     , projdate  resultset order id, project

results in 

id  fname  lname     project  projdate   --  -----  --------  -------  --------    1  john   doe       proja    20150701    1  john   doe       projb    20150801    1  john   doe       projc    20150901    2  jane   smith     projc    20150902    2  jane   smith     projd    20150701    3  lisa   anderson  projb    20150801    3  lisa   anderson  projc    20150903    4  nancy  johnson   projb    20150601    4  nancy  johnson   projc    20150822    4  nancy  johnson   proje    20150904    5  chris  edwards   proja    20150905

this algorithm handles project , date lists of equal length. should 1 list shorter other given row, special attention needed apply null in proper place.

-- cleanup drop table #tinputdata

Comments

Post a Comment

Popular posts from this blog

javascript - Using jquery append to add option values into a select element not working -

i'm looking dynamically populate select element names of items stored array. for reason it's not working , i'm not sure why. html: <div class="col-md-4 col-md-offset-4"> <select class="select1"> </select> , <select class="select2"> </select> </div> note i'm using bootstrap layout. here's jquery i'm using try , populate ".select1" jquery: select: function() { $.each(state.greens, function(index, value) { $(".select1").append("<option value =' " + index + " '>" + state.greens[index].name + "</option>"); }); } note function 'select' stored within object called 'display'. state.greens name of array i'm trying access. so @ end of html page call display.select(); call function. no error messages showing in console. also, saw question: appending
Read more

Android soft keyboard reverts to default keyboard on orientation change -

i'm building android soft keyboard , can't seem fix bug - have arabic , qwerty keyboard , when rotate device on qwerty keyboard (or arabic shift), it's if program has "restarted" , becomes arabic keyboard without shift. the onsaveinstancestate(bundle savedinstancestate) not work because application not extend activity inputmethodservice . i put following in android manifest android:configchanges="keyboard|keyboardhidden|orientation" android:windowsoftinputmode="stateunchanged|adjustresize"> i tried using @override public void onconfigurationchanged(configuration newconfig) { super.onconfigurationchanged(newconfig); log.i(mydebug, "config changed " + currentkeyboard.equals(qwerty)); } however, currentkeyboard.equals(qwerty)) results false , made sure true before orientation change. any appreciated. i think applications fault. if application restarting on orientation change
Read more

Rendering JButton to get the JCheckBox behavior in a JTable by using images does not update my table -

i rendering jbutton image on simulate jcheckbox behavior. why don't let jtable render jcheckbox? because need bigger checkboxes , there's no way can resize them (as far know). so, have own table cell renderer , editor. both renders jbutton , checkbox images on them. trying change image checked image unchecked image (or vice versa) , update cell value (boolean true or false). however, reason, getcelleditorvalue() not update table. so, release mouse, value goes original one. any appreciated! lot! public class tableexample extends jframe { jscrollpane pane; jpanel panel; jtable table; object[][] data; mytablemodel tm; renderer buttoncolumn; public tableexample(object[][] data) throws ioexception { string[] columns = {"column#1", "column#2", "column#3", "column#4", "column#5", "column#6"}; this.data = data; this.setpreferredsize(new dimension(700, 500));
Read more