Well, I had added 2 more articles to my blog: http://firebird21.wordpress.com/2013/12/28/creando-tablas-dentro-de-un-stored-procedure-o-de-un-trigger/
http://firebird21.wordpress.com/2013/12/28/creando-tablas-agregadas-e-insertandoles-datos/ There, I explain a way you can get that you want. Any doubt, just write me. Greetings. Walter. On Sat, Dec 28, 2013 at 4:00 AM, Venus Software Operations < venussof...@gmail.com> wrote: > > > Thanks Walter. I was hoping to get the columns appear on their own rather > than individually predicting them (but if that is not at all possible > well). I did have a look at your link and also the ones for Cross-Tab > which was more nearer to what I wanted. Please see my present actual query > and it's result > > SELECT r.IPID, m.IID, m.CCODE, r.BAMT > FROM SSALEINVOICEFOOTER r > JOIN MACCOUNTS m > on m.IID = r.IACCOUNTID > ORDER by r.IPID > > 8 12 SALE 25000.000000 > 8 64 CENVAT 3000.000000 > 8 65 ECESS 60.000000 > 8 66 SHCESS 3000.000000 > 8 21 31060.000000 > 8 60 ST 1242.400000 > 8 63 ADDLVAT 310.600000 > 8 121 ST 32613.000000 > 16 12 SALE 3958.420000 > 16 587 EXP 3958.420000 > 17 12 SALE 148000.000000 > 17 588 148000.000000 > 20 12 SALE 27072.000000 > 20 64 CENVAT 3248.640000 > 20 65 ECESS 64.970000 > 20 66 SHCESS 32.490000 > > What I need to do is convert the above data based on the first column > r.iPID being rows. m.cCODE being the column header and r.bAMT being the > values for each column. This is what is the end result I was looking > towards. Please note ST column for row iPID = 8 is a sum total of two ST > cCodes in the same iPID > > iPID, SALE, CENVAT, ECESS, SHCESS, ST, ADDLVAT, EXP > 8, 25000.000000, 3000.000000, 60.000000, 3000.000000, 33855.400000, > 310.600000, NULL > 16, 3958.420000, NULL, NULL, NULL, NULL, NULL, 3958.420000 > 17, 148000.000000, NULL, NULL, NULL, NULL, NULL, NULL > 20, 27072.000000, 3248.640000, 64.970000, 32.490000, NULL, NULL, NULL > > > Please advise > > Thanks and regards > Bhavbhuti > > > > > > On 28-12-2013 02:26 am, W O wrote: > > > Well, it is not complicated at all, I was waiting for somebody writing a > better technique but the days go on and that not happen then I show you a > link to a page where the technique is described, with two examples. The > page is in Spanish but you can use some translator (Google has one). And of > course, if you have any question, just tell me, here or in the page: > > > http://firebird21.wordpress.com/2013/11/24/convirtiendo-filas-en-columnas/ > > One drawback of the technique is that you need to know in advance the > exact number of columns or at least the maximum number of columns. > > Greetings. > > Walter. > > > > > > On Fri, Dec 27, 2013 at 7:28 AM, Svein Erling Tysvær < > svein.erling.tysv...@kreftregisteret.no> wrote: > >> >> >Hi all >> > >> >Merry Christmas everyone! >> > >> >I have had to revisit this issue. I have a simplified table >> >Color, Amount >> >Red, 123 >> >Green, 234 >> >Blue, 345 >> > >> >Previously, I have been advised of CTEs and CASE before wherein I was >> able to get the result but for it I had to assume that such and such Colors >> names existed. >> >But the thing is that the Color value is not under control and is not a >> fixed list, so one user could have a >> >Light Green, 456 >> >and another user could define the same as >> >Pale Green, 567 >> >I want the colors to become the header in the output and the Amount >> underneath,something like >> > >> >Red, Green, Blue, Light Green, Pale Green >> >123, 234, 345, 456, 567 >> > >> >Please advise >> >> Hi Bhavbhuti! >> >> SQL - at least the way Firebird implements it - is basically lousy at >> turning rows into columns, this is work more suitable for something like >> Excel (PivotTable), FastReport or some other tools/components. I think it >> is doable in Firebird using EXECUTE STATEMENT inside EXECUTE BLOCK, but it >> sounds more complicated than the alternatives mentioned above. I think one >> of the reasons for this being complicated to do in SQL, is that you >> normally need to know the number and type of columns in advance. >> >> Sorry, >> Set >> > > > -- > > > Thanking you. > > Yours Faithfully, > For Venus Software Operations > ---- > Mr. Bhavbhuti Nathwani > ___________________________________________ > Softwares for Indian Businesses at: http://www.venussoftop.com > venussoftop@gmail.comvenussof...@mail.ru > ___________________________________________ > > Please note: We reserve complete rights for policy changes in the future and > the same will be applicable immediately as and when made. Attachments may > get corrupted before reaching you, in such a situation please let us know and > we will resend you the same at the earliest. We do not take any > responsibility for data loss of any type and kind. Data safety remains the > sole the responsibility of the users of our softwares. > ___________________________________________ > > Internet email confidentiality: > > This message may contain information that may be privileged or confidential. > If you are not the addressee nor are you responsible for the delivery of the > message to the addressee indicated in this email, then you may not copy or > deliver this email to anyone and you should notify the sender by reply email > and then destroy this message. > > Please reply email immediately to this message with REMOVE in the subject, if > you or your employer do not consent to email of this kind. > > Opinions, conclusions and other information in this message that do not > relate to the official business of my firm shall be understood as neither > given nor endorsed by my company. > > >
