sql server pivot query - help required -


i need write sql server query pivot data source table.

my source table looks - 

cust_id  item1_desc_count   item2_desc_count   item3_desc_count -------  ----------------   ----------------   ------------ cust1    10                 12                 9 cust2    7                  1                  3 cust3    12                 6                  0 ...

item master table looks -

item_id item_desc ------- --------- 1       item1_desc 2       item2_desc 3       item3_desc

please note item descriptions used in column names in source table.

and need output - 

cust_id   item_id   count --------  --------- ------ cust1     1         10 cust1     2         12 cust1     3         9 cust2     1         7 cust2     2         1 cust2     3         3 cust3     1         12 ...

can me achieve using sql query?

this more dynamic approach. no need specify item count columns.

the cross apply unpivot source table.

i should add unpivot more performant, i'm assuming have many count columns.

create sample data

declare @source table (cust_id varchar(25),item1_desc_count int,item2_desc_count int,item3_desc_count int) insert @source values ('cust1',10,12,9), ('cust2', 7, 1,3), ('cust3',12, 6,0)  declare @item table (item_id int,item_desc varchar(50)) insert @item values (1,'item1_desc'), (2,'item2_desc'), (3,'item3_desc')

code example

select b.cust_id       ,c.item_id       ,count = b.value   (select xmldata = cast((select * @source xml raw) xml))  cross apply (                 select cust_id = r.value('@cust_id','varchar(50)')                       ,item    = attr.value('local-name(.)','varchar(100)')                       ,value   = attr.value('.','varchar(max)')                    a.xmldata.nodes('/row') a(r)                  cross apply a.r.nodes('./@*') b(attr)                  attr.value('local-name(.)','varchar(100)') not in ('cust_id','otherfieldstoexclude')              ) b  join @item c on b.item c.item_desc+'%'

returns

cust_id item_id count cust1   1       10 cust1   2       12 cust1   3       9 cust2   1       7 cust2   2       1 cust2   3       3 cust3   1       12 cust3   2       6 cust3   3       0

edit - unpivot option

select a.cust_id       ,b.item_id       ,count = a.value  (         select cust_id,item,value         @source          unpivot ( value item in (item1_desc_count,item2_desc_count,item3_desc_count) ) u         ) join @item b on a.item b.item_desc+'%'

-

Comments

Post a Comment

Popular posts from this blog

python - How to insert QWidgets in the middle of a Layout? -

Image
i'm using qt framework build graphical user interface. use qgridlayout position qwidgets neatly. gui looks this: my application regularly adds new widgets gui @ runtime. these new widgets not added @ end of qlayout, somewhere in middle. the procedure bit cumbersome. applied on figure above, need take out widg_c , widg_d , ... qgridlayout. next, add widg_x and widg_y , , put other widgets again. how remove widgets qgridlayout: for in reversed(range(mygridlayout.count())): self.itemat(i).widget().setparent(none) ### as long you're dealing small amount of widgets, procedure not disaster. in application display lot of small widgets - perhaps 50 or more! application freezes second while procedure ongoing, annoying user. there way insert widgets somewhere in qlayout, without need take out other widgets? edit: apparently solution qvboxlayout simple. use function insertwidget(..) instead of addwidget(..) . docs can found link: http://doc.qt.io/qt-5/qboxlayou
Read more

python - serve multiple gunicorn django instances under nginx ubuntu -

i need serve 2 webs apps (django) on gunicorn under different domain names using nginx on ubuntu. started using tutorial: https://www.digitalocean.com/community/tutorials/how-to-set-up-django-with-postgres-nginx-and-gunicorn-on-ubuntu-16-04 it worked fine 1 using that. tried same thing second gives me 502 gateway second domain. tried following tutorial: http://michal.karzynski.pl/blog/2013/10/29/serving-multiple-django-applications-with-nginx-gunicorn-supervisor/ i'm @ part run gunicorn start script: sudo bin/gunicorn_start i response: starting webuildblack root traceback (most recent call last): file "/home/devin/webuildblack/bin/gunicorn", line 7, in <module> gunicorn.app.wsgiapp import run importerror: no module named 'gunicorn.app'; 'gunicorn' not package you have named program gunicorn.py gunicorn -package. rename file , remove gunicorn.pyc file.
Read more

module - Prestashop displayPaymentReturn hook url -

i new prestashop developer , trying create paymentmodule. have got show payment method can not proceed purchase because not know hot works. does know should redirect run hooddisplaypaymentreturn method? i happy if explain me complete navigation map make purchase. anyway, can find relation between hooks , pages? when have new module payment rely on simplest provided prestashop: bankwire. inside can find 3 hooks. hookpayment: public function hookpayment($params) { if (!$this->active) return; if (!$this->checkcurrency($params['cart'])) return; $this->smarty->assign(array( 'this_path' => $this->_path, 'this_path_bw' => $this->_path, 'this_path_ssl' => tools::getshopdomainssl(true, true).__ps_base_uri__.'modules/'.$this->name.'/' )); return $this->display(__file__, 'payment.tpl'); } hookdisplaypaymenteu: public fun
Read more