SQL Pivot Column that has multiple values for same column -


trying pivot table results may have multiple rows same value have data looks far.

nbr      person     test  33      barry.      prim 33      brian       sup 33      burke rt    1st 33      ray         add 33      jake        add 33      smith       add

i'm trying pivot looks this:

nbr   prim    sup     1st       add   add2       add3  33   barry   brian   burke      ray   jake       smith

this have far normal pivot doesn't work grab ones same value in test column

create table #testtbl(nbr int,name varchar(20),test varchar(10)) insert #testtbl select '33','barry','prim' union select '33','brian','sup' union select '33','burke','1st' union select '33','ray','add' union select '33','jake','add' union select '33','smith','add'   select * ( select *  #testtbl ) x  pivot(  max(name) test in ([prim],[sup],[1st],[add]) ) pivot1

any appreciated. if not possible have columns output add add2 , add3 thats fine. whatever works.

you can modifying test value using window functions:

select * (select tt.name,              (test + (case when count(*) on (partition test) = 1                            ''                            else cast(row_number() on (partition test order (select null)) varchar(255))                       end)) test       testtbl tt      ) x pivot(  max(name) test in ([prim], [sup], [1st], [add1], [add2], [add3]) ) pivot1

a sql fiddle here.


Comments

Post a Comment

Popular posts from this blog

Magento/PHP - Get phones on all members in a customer group -

i working on custom magento extension. working around adminhtml part , i've created custom form there. here form code: <?php class vivasindustries_smsnotification_block_adminhtml_sms_sendmass_edit_form extends mage_adminhtml_block_widget_form { public function _preparelayout() { $extensionpath = mage::getmoduledir('js', 'vivasindustries_smsnotification'); $head = $this->getlayout()->getblock('head'); $head->addjs('jquery.js'); $head->addjs('vivas.js'); return parent::_preparelayout(); } protected function _prepareform() { $form = new varien_data_form(array( 'id' => 'edit_form', 'action' => $this->geturl('*/*/save', array('id' => $this->getrequest()->get...
Read more

php - Bypass Geo Redirect for specific directories -

i'm trying bypass redirect specific url within site. wondering if might able help. the code below code use, original urls replaces example.com i'd able access http://example.com/admin without redirect occuring. is there way this, , there way using own ip? function country_geo_redirect() { $country = getenv('http_geoip_country_code'); if ($country == "us" ) { wp_redirect("http://usa.example.com/"); exit; } else if ($country == "gb" ) { wp_redirect("http://uk.example.com/"); exit; } } add_action('init', 'country_geo_redirect'); you can bypass redirection code if server found ip address. if ip address == <my ip address> ' nothing here else country_georedirect() end if
Read more

php - .htaccess mod_rewrite for dynamic url which has domain names -

i want convert url ex:( example.com/link.php?url=facebook.com ) ( example.com/facebook.com.php ) how can using .htaccess. or there other process make job easier ? i tried adding script .htaccess file options +followsymlinks rewriteengine on rewriterule url-(.*)\.htm$ link.php?url=$1 but converting url adding "url-" ( example.com/url-facebook.com.php ) i want remove "url-" , want make ( example.com/facebook.com.php ) rewriteengine on rewritecond %{the_request} ^get.*link\.php\?url\= [nc] rewritecond %{request_uri} !/system/.* [nc] rewriterule (.*?)link\.php\?url\=/*(.*) /$1$2 [r=301,ne,l] not sure how above work edit of used use remove index.php url
Read more