How to get output in following formats

Question

How to get output in following formats

amitsingh308

Ten Centuries

Points: 1287
More actions
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??

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

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

R.P.Rozema SSChampion Points: 12317 More actions · Answer 1

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?

amitsingh308 Ten Centuries Points: 1287 More actions · Answer 2

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.

R.P.Rozema SSChampion Points: 12317 More actions · Answer 3

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?

mr_opto Ten Centuries Points: 1214 More actions · Answer 4

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