sql - Dynamically display rows as columns -



sql - Dynamically display rows as columns -

i couldn't think of way word title, if can come improve please sense free. there old vb6 app pulls info db have more or less restructured , gives user dump of of product info @ once. need inner joins of these tables together. know how basic inner joins stuck on 1 detail. there few tables there multiple entries each item. example, crossreference table may have multiple cross reference numbers item, or may have one, or may have none @ all. possible have placed dynamically separate columns. this:

item crossreferencenumber xxxxx crossref1 xxxxx crossref2 xxxxx crossref3

could become (after bring together other tables):

item basepart size crossreferencenumber1 crossreferencenumber2 crossreferencenumber3 xxxx xxxx big crossref1 crossref2 crossref3

but if there no cross references, there no cross reference columns. possible or dreaming?

oracle 11g , sql server 2005+ both contain pivot command accomplish want.

http://www.orafaq.com/wiki/pivot

http://msdn.microsoft.com/en-us/library/ms177410.aspx

otherwise need build dynamic sql statement accomplish this.

edit - here go (sql server version).

/* begin set of test info */ if exists (select 1 sys.tables name = n'item') drop table item go if exists (select 1 sys.tables name = n'crossreference') drop table crossreference go create table item ( item varchar(20), basepart varchar(20), size varchar(20) ); create table crossreference ( item varchar(20), crossreferencenumber varchar(20) ); insert item values ('item1', 'b1', 'large'); insert item values ('item2', 'bxx1', 'large'); insert item values ('item3', 'bddf1', 'small'); insert item values ('item4', 'be3f1', 'small'); insert item values ('item5', 'b13vx1', 'small'); insert crossreference values( 'item1', 'crossref1') insert crossreference values('item1', 'crossref2') insert crossreference values('item1', 'crossref3') insert crossreference values('item1', 'crossref4') insert crossreference values('item2', 'crossref1') insert crossreference values('item2', 'crossref1') insert crossreference values('item3', 'crossref1') insert crossreference values('item4', 'crossref2') insert crossreference values('item5', 'crossref5') insert crossreference values('item5', 'crossref1') insert crossreference values('item5', 'crossref2') insert crossreference values('item5', 'crossref3') /* end of test info setup */ /* begin of actual query */ declare @xrefs varchar(2000), @query varchar(8000) select @xrefs = stuff((select distinct '],[' + ltrim(crossreferencenumber) crossreference order '],[' + ltrim(crossreferencenumber) xml path('') ), 1, 2, '') + ']' set @query = 'select * item inner bring together ( select * ( select item, crossreferencenumber crossreference ) t pivot (max(crossreferencenumber) crossreferencenumber in (' + @xrefs + ')) pvt ) xrefs on i.item = xrefs.item order i.item' execute (@query) /* end */

sql sql-server-2008 dynamic inner-join

Comments

Post a Comment

Popular posts from this blog

iphone - Dismissing a UIAlertView -

iphone - Dismissing a UIAlertView - i set in app purchase app, , when user taps button, purchase started. basically, tap button, , depending on speed on net connection, waiting 10 seconds until new alert view comes asking if purchase product. user tap button multiple times since nil came up, , multiple purchase alert views come up. additionally, maybe seen user app bug. in end, problem. i want alert view come spinning wheel says "loading..." when users taps purchase button. problem is, how dismiss when new alert view comes asking user if want purchase product? if ([uialertview alloc] says: @"whatever apple's alert view says") { //dismiss "loading..." alert view here } i uncertainty work, input appreciated. thanks! you need have access alertview. can this. create alertview instance var in app delegate , when want show loading initialize instance var assign property , when want dismiss phone call [alertviewinstance d...
Read more

intellij idea - Update external libraries with intelij and java -

intellij idea - Update external libraries with intelij and java - simple plenty really. i'm using apache jars, i'll create changes jar's every often. these jars listed external libs in intelij (i.e classpath looking install dir of jar's). when create changes intelij doesn't seem know new implementation. have remove jar external library , re-ad it. does know have intelij picks changes automatically ? i've done clean , rebuild project had little effect. you can go preferences -> build, execution, deployment -> build tools -> maven -> importing , check box says import maven projects automatically . in mac, can command + shift + a , come in action reimport , click on reimport maven projects . intellij-idea
Read more

javascript - send data from a new window to previous window in php -

javascript - send data from a new window to previous window in php - i have image in main.php file : <img src="..\images\image.gif" class="cur" onclick="imgwin();"> when click on image below function has called : function imgwin() { imagewin=window.open("../pages/img.php","imagewin","toolbar=no,width=500,height=200,directories=no,menubar=no,scrollbars=yes"); } this function opens img.php in new window img.php file : <html> <head> <title>upload image</title> </head> <body> <form name="imgform" method="get" action="#"> address : <input type="file" name="imgurl" size="50"> description : <input type="text" name="description" size="50"> <input type="submit" value="sumbit...
Read more