Post reply

How to get output in following formats

  • September 27, 2011 at 1:22 am

    #257977

    I writing a query as follow

    Select 'I87' [Location],PC.Company_Name [Company Name], Q.Department,PG.Group_Name [Group Name],

    PS.SGroup_Name [Sub Group Name], PB.Item_Code[Item Code],PB.Item_Name [Item Name],

    Sum(isnull(BD.Qty,0)) [Quantity], Sum(isnull(BM.Net_Amount,0))[Bill Amount] from I87.I87.Bill_Master BM

    inner join I87.I87.Bill_Detail BD on BM.BillNoWPrefix=BD.BillNoWPrefix

    inner join Packing_Belongs PB on PB.Item_Code=BD.Item_Code

    inner join Prop_Company PC on PC.Company_ID=PB.Company_ID and PC.Company_ID='2'

    inner Join PROP_Groups PG on PG.Group_Id=PB.Group_ID and PG.Group_Id='49'

    inner join Prop_SGroup PS on PS.SGroup_Id=PB.SGroup_ID and PS.SGroup_Id='32'

    inner join (Select Distinct Department from Packing_Belongs) Q on Q.Department=PB.Department and Q.Department='Bakery'

    where BM.Bill_Date between '9/5/2011' and '9/20/2011'

    group by PB.Item_Code,PB.Item_Name,PG.Group_Name,PS.SGroup_Name,Q.Department,PC.Company_Name

    and its output is as follow..

    [p] Location , Company Name, Department, Group Name, Sub Group Name Item Code, Item Name, Quantity, Amount[/p]

    But I required output as following format as shown in Image attached with this query..

    can any one tell me how can I do so??

  • September 27, 2011 at 4:40 am

    #1387027

    Your image link is broken so you will probably not get any correct answers. But don't mind fixing that. Instead, please click 'Posting Data Etiquette' in my footer text and read how to complete your question with a DDL and sample data script. Once you've completed that, hordes of people will jump in to help you.


    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]

    If you don't have time to do it right, when will you have time to do it over?

  • September 28, 2011 at 12:49 am

    #1387570

    I need output as below...

    Location 1 Location 2 Location 3

    Item Code Item Name Qty Bill Amount Qty Bill Amount Qty Bill Amount

    01 Abc 5 20.00 2 8.00 8 32.00

    now columns QTY and Bill Amount should repeat per as number of Locations Every location should be displayed in header of these two columns

    actually I need to generate sale report according to the location as format mentioned above.

  • September 28, 2011 at 3:55 am

    #1387632

    It's this link[/url] I'm talking about. If you do not supply the information we need, that will be an opportunity missed for you.


    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]

    If you don't have time to do it right, when will you have time to do it over?

  • September 28, 2011 at 5:38 am

    #1387675

    As the previous poster mentioned, following the best practices will allow us to understand the issue at hand and avoid misunderstandings.

    However, at a glance it looks like your issue may be helped by reading up on the WITH ROLLUP option:

    http://msdn.microsoft.com/en-us/library/ms189305%28v=sql.90%29.aspx

    However, I just want to clarity something... are you having issues with the data that you're getting from that query or are you actually trying to format it into a readable format using the query itself?

    It is still rather difficult to glean what your problem is based opn the information provided to us.

    Regards,

    JC

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply