Hi Dear SET (A Real Master-Trouble Shooter),


Just give me some time, I hope I may do it with your suggested sql.

Sorry I have one doubt, you gave me following code, please let me know, what it is for ?

WITH Branches(BranchCode) as

(SELECT DISTINCT sBrachCode

FROM Book_Details

WHERE (sYearMonth = :YearMonthA or sYearMonth = :YearMonthB)

AND BookName in(:MyFirstBookName, :MySecondBookName)

)


Thank You Very Much And With Best Regards.

Vishal

--- On Wed, 8/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: Wednesday, 8 September, 2010, 3:34 AM







 









--- In firebird-support@yahoogroups.com, Vishal Tiwari <vishualsoft@...> wrote:

>

> Dear Set (A Real Master-Trouble Shooter),

>  

> Let me ask you one question, if I know the name of those books for which I need to print those respective columns then is it easy to write sql ?

>  

> I am ready to get books name at application level (i.e. coding level) and if required I would make the required sql using loop in my application and the this looped sql I would merge (concatinate) with original "Select ..." SQL.

>  

> Is it possible, or if you have any suggestion by which I could get data using only sql, or any this I need to do at application level i.e. any thing required to do at application level and finaly I make the sql, would I get such logic ?

>  

Sure, if you know the name of the books, things are simpler and you can even name the columns as you want (I've only named three fields, but this part is a no-brainer if you can get the name from an application before creating the SQL statement). I assume you're only interested in branches actually having sold the books below:



WITH Branches(BranchCode) as

(SELECT DISTINCT sBrachCode

FROM Book_Details

WHERE (sYearMonth = :YearMonthA or sYearMonth = :YearMonthB)

AND BookName in(:MyFirstBookName, :MySecondBookName)

)



SELECT B.BranchCode, BD1A.rBook_Price as Book1PriceYearMonthA, BD1B.rBook_Price as Book1PriceYearMonthB,

coalesce(BD1A.iCopy_Sold, 0) - coalesce(BD1B.iCopy_Sold, 0) as Book1CopySoldDifference,

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

LEFT JOIN Book_Details BD1A ON B.BranchCode = BD1A.BranchCode

AND BD1A.BookName = :MyFirstBookName

AND BD1A.sYearMonth = :YearMonthA

LEFT JOIN Book_Details BD1B ON B.BranchCode = BD1B.BranchCode

AND BD1B.BookName = :MyFirstBookName

AND BD1B.sYearMonth = :YearMonthB

LEFT JOIN Book_Details BD2A ON B.BranchCode = BD2A.BranchCode

AND BD2A.BookName = :MySecondBookName

AND BD2A.sYearMonth = :YearMonthA

LEFT JOIN Book_Details BD2B ON B.BranchCode = BD2B.BranchCode

AND BD2B.BookName = :MySecondBookName

AND BD2B.sYearMonth = :YearMonthB



HTH,

Set
























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