On Sat, Dec 28, 2013 at 4:00 AM, Venus Software Operations <venussoftop@...> 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.tysvaer@...> 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@...
venussoftop@...
___________________________________________

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.