query to display data seprated by items

Question

0.00/5 (No votes)

See more:

I want output like this

item_type:
Vegetables 
itemNumber  ItemName   quantity   price
101         Potato      10       20.00
102         Tomato     102       10.00
item_type:
Fruits:
itemNumber  ItemName   quantity   price
 103        Mango       2         30.00
 104        Apple       4         40.00

I tried the following query

SQL

select item_number,item_type,item_name ,quantity ,price from purchase_order order by item_type;

but the output is like this

item_number	item_type	item_name	quantity	price
3	   	fruits	         Dates          3	        57.900
6	        fruits		 Jackfruit      34              10.890	
1	        vegetables       Ladies Finger	23	        45.000
3	        vegetables       Broad Beans	23	        53.900

I tried group by clause bt it needs aggregate function

give me an idea how to do get that output

Posted 7-May-12 23:31pm

kevin_ze

Updated 8-May-12 0:14am

v2

Add a Solution

5 solutions

Solution 1

add group by to item_type so you will get item list according to item_type

Posted 7-May-12 23:43pm

PrashantShirke

Solution 2

Add the column item_type in both the tables

Posted 8-May-12 0:21am

Arul R Ece

Solution 3

here am using single table and column item_type is already there in that table

Posted 8-May-12 0:38am

kevin_ze

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2012-05-08T00:41:00

Solution 5

Query is totally correct! You need to change the order of fields:

SQL

SELECT item_type, item_number, item_name, quantity ,price
FROM purchase_order
ORDER BY item_type

You you want to group records by item_type, you need to use reports to get this functionallity.
Step by Step Creation of Crystal Report using its Features Group, Graph, Cross-Tab and Sub Report[^]
ReportViewer[^]

Posted 8-May-12 0:41am

Maciej Los

Comments

kevin_ze 8-May-12 8:20am

Thanks,
that crystal report link is very useful to me

Maciej Los 8-May-12 9:03am

You're welcome ;)
Can you rate my answer?

Reegan Durai.m · Accepted Answer · 2012-05-08T00:39:00

Solution 4

u create stored procedure send parameter in ur item type and display it.

ALTER PROCEDURE purchase_order
@ItemType string
AS
BEGIN
select itemno, itemname, qualityprice as Balance from PaymentLedger
WHERE ItemType = @ItemType

END

Posted 8-May-12 0:39am

Reegan Durai.m

Comments

kevin_ze 8-May-12 8:19am

Thank u very much for ur answer...