Hi SET,
 
Thank You very much Ya....
 
Let me give a try.
 
And would get back to you again.
 
With Best Regards.
 
 
Vishal

--- On Tue, 7/9/10, Svein Erling <svein.erling.tysvaer@...> wrote:


From: Svein Erling <svein.erling.tysvaer@...>
Subject: [firebird-support] Re: Data in Row Format, Need To Display In Columun Format
To: firebird-support@yahoogroups.com
Date: Tuesday, 7 September, 2010, 2:55 AM


 



Hi Vishal!

Generally, I prefer to flatten datasets on the client, e.g. using PivotTable in Excel.

However, if you insist on doing it on the server, you could try something like

WITH RECURSIVE BookNames (BookNo, BookName) as
(SELECT MIN(CAST(1 AS INTEGER)), MIN(sBook_Name)
FROM Book_Details
WHERE sYearMonth = :YearMonthA or sYearMonth = :YearMonthB)
UNION ALL
SELECT MIN(BN.BookNo+1), MIN(BD.sBook_Name)
FROM BookNames BN
JOIN Book_Details BD ON BN.BookName < BD.sBook_Name
WHERE (BD.sYearMonth = :YearMonthA or BD.sYearMonth = :YearMonthB)
AND BN.BookNo < 2,
Branches(BranchCode) as
(SELECT DISTINCT sBrachCode
FROM Book_Details
WHERE sYearMonth = :YearMonthA or sYearMonth = :YearMonthB)

SELECT B.BranchCode, BD1A.rBook_Price, BD1B.rBook_Price, coalesce(BD1A.iCopy_Sold, 0) - coalesce(BD1B.iCopy_Sold, 0),
coalesce(BD1A.Tot_Amount, 0) - coalesce(BD1B.Tot_Amount, 0),
BD2A.rBook_Price, BD2B.rBook_Price, coalesce(BD2A.iCopy_Sold, 0) - coalesce(BD2B.iCopy_Sold, 0),
coalesce(BD2A.Tot_Amount, 0) - coalesce(BD2B.Tot_Amount, 0)
FROM Branches B
JOIN BookNames BN1 ON BN1.BookNo = 1
JOIN BookNames BN2 ON BN2.BookNo = 2
LEFT JOIN Book_Details BD1A ON B.BranchCode = BD1A.BranchCode
AND BN1.BookName = BD1A.BookName
AND BD1A.sYearMonth = :YearMonthA
LEFT JOIN Book_Details BD1B ON B.BranchCode = BD1B.BranchCode
AND BN1.BookName = BD1B.BookName
AND BD1B.sYearMonth = :YearMonthB
LEFT JOIN Book_Details BD2A ON B.BranchCode = BD2A.BranchCode
AND BN2.BookName = BD2A.BookName
AND BD2A.sYearMonth = :YearMonthA
LEFT JOIN Book_Details BD2B ON B.BranchCode = BD2B.BranchCode
AND BN2.BookName = BD2B.BookName
AND BD2B.sYearMonth = :YearMonthB

Except using a stored procedure or possibly EXECUTE BLOCK (which I haven't even tried yet), I don't know how to get the correct titles of the columns. If you want more than two books, you change (BN.BookNo < 2) to the number you want (you must have a fixed value, SQL doesn't allow for a variable number of columns - that's one reason why I prefer pivot tables) and add BD3A, BD3B, BD4A, BD4B...

HTH,
Set










[Non-text portions of this message have been removed]