How do I... Multiple row records in single row line and other single column

Question

1.00/5 (1 vote)

See more:

i have a purchase order tablepo and there are many multiple PO and Its item list and i want to show in a page just single Purchase number and single from ware house to warehouse

Purchase_order	From_warehouse
9211	Hyderabad Station Roard-HSR
9211	Hyderabad Station Roard-HSR
9211	Hyderabad Station Roard-HSR
9211	Hyderabad Station Roard-HSR
9211	Hyderabad Station Roard-HSR
9211	Hyderabad Station Roard-HSR
123	Hyderabad Canttoment-HCT
123	Hyderabad Canttoment-HCT

and i want result query

Purchase_order	From_warehouse
9211	Hyderabad Station Roard-HSR
123	Hyderabad Canttoment-HCT

What I have tried:

and i want result query 
<pre>Purchase_order	From_warehouse
9211	Hyderabad Station Roard-HSR
123	Hyderabad Canttoment-HCT

Posted 5-Sep-19 9:29am

irfanansari

Updated 5-Sep-19 10:57am

Add a Solution

Comments

phil.o 5-Sep-19 16:03pm

You misformed the "What I have tried" part; you are supposed to detail what searches you have performed, and the code you wrote from these that is giving you some troubles.
Please use the Improve question widget which appears on hovering your question, and try to respect the posting guidelines of this forum.

irfanansari 6-Sep-19 12:43pm

you always have negative mind set that why you take wrong to persons not ablity you have to solve help but just creatisies person open your eyes and see bellow a good man good person reply and solved my issue

2 solutions

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)

OriginalGriff · Accepted Answer · 2019-09-05T10:50:00

Solution 1

Try:

SQL

SELECT Purchase_order, From_warehouse
FROM MyTable
GROUP BY Purchase_order, From_warehouse

Posted 5-Sep-19 10:50am

OriginalGriff

Comments

irfanansari 6-Sep-19 13:11pm

sir thanks a lot for help but there is a problem in this if first colum purchase order and second order same then it shows single reocrd but it is not works when purchase order colum and from warehouse diffrents then it is show all records not single as see bellow
Purchase_order, From_warehouse
003 Hyderabad Station Roard-HSR
004 Hyderabad Station Roard-HSR
007 Hyderabad Canttoment-HCT
007 Hyderabad Station Roard-HSR
008 Hyderabad Station Roard-HSR
123 Hyderabad Station Roard-HSR
786 Hyderabad Canttoment-HCT
9211 Hyderabad Canttoment-HCT
9211 Hyderabad Station Roard-HSR

please sir

i want just single actually if you guide me more then its good i am new in this i am created a table in this i add there many times inserts all fields as date purchase order number from ware house and to ware house

ZurdoDev · Accepted Answer · 2019-09-05T10:57:00

Solution 2

Another option is to use DISTINCT.

SQL

SELECT DISTINCT Purchase_order,	From_warehouse
FROM YourTable

This gives you unique records and in my opinion is better than GROUP BY because it is more clear as to your intent.

Posted 5-Sep-19 10:57am

ZurdoDev

Updated 5-Sep-19 10:59am

v2

Comments

irfanansari 6-Sep-19 13:04pm

sir thanks a lot for help but there is a problem in this if first colum purchase order and second order same then it shows single reocrd but it is not works when purchase order colum and from warehouse diffrents then it is show all records not single as see bellow
Purchase_order, From_warehouse
003 Hyderabad Station Roard-HSR
004 Hyderabad Station Roard-HSR
007 Hyderabad Canttoment-HCT
007 Hyderabad Station Roard-HSR
008 Hyderabad Station Roard-HSR
123 Hyderabad Station Roard-HSR
786 Hyderabad Canttoment-HCT
9211 Hyderabad Canttoment-HCT
9211 Hyderabad Station Roard-HSR

please sir

i want just single actually if you guide me more then its good i am new in this i am created a table in this i add there many times inserts all fields as date purchase order number from ware house and to ware house

ZurdoDev 6-Sep-19 13:24pm

I do not understand what you are asking. Given the data you just shared, what would your expected output be?

irfanansari 6-Sep-19 13:25pm

sir this is querry i search from net and its works but not exact as i want
and i just want to learn mean expalin for me if you can

DECLARE @txtitemname VARCHAR(8000)
SELECT @txtitemname = COALESCE(@txtitemname + ', ', '') + txtitemname FROM tblpo
SELECT distinct txttrannumber, @txtitemname AS Transation_number FROM tblpo

ZurdoDev 6-Sep-19 13:58pm

You are not being clear on what you want.

irfanansari 7-Sep-19 9:24am

sir actually this is query i found on internet for get single record can you just explain it for me for understanding

DECLARE @txtitemname VARCHAR(8000)
SELECT @txtitemname = COALESCE(@txtitemname + ', ', '') + txtitemname FROM tblpo
SELECT distinct txttrannumber, @txtitemname AS Transation_number FROM tblpo

irfanansari 7-Sep-19 9:29am

SELECT DISTINCT Purchase_order, From_warehouse
FROM YourTable

Result this as bellow

Purchase_order, From_warehouse
003 Hyderabad Station Roard-HSR
004 Hyderabad Station Roard-HSR
007 Hyderabad Canttoment-HCT
007 Hyderabad Station Roard-HSR
008 Hyderabad Station Roard-HSR
123 Hyderabad Station Roard-HSR
786 Hyderabad Canttoment-HCT
9211 Hyderabad Canttoment-HCT
9211 Hyderabad Station Roard-HS

this is double again

ZurdoDev 7-Sep-19 11:08am

What is doubled? I have already asked you what you want the result to look like and you won't answer. We can't help you unless you answer.

irfanansari 8-Sep-19 8:04am

sir i have a database as
Purchase_order, From_warehouse
003 Hyderabad Station Roard-HSR
004 Hyderabad Station Roard-HSR
007 Hyderabad Canttoment-HCT
007 Hyderabad Station Roard-HSR
008 Hyderabad Station Roard-HSR
123 Hyderabad Station Roard-HSR
786 Hyderabad Canttoment-HCT
9211 Hyderabad Canttoment-HCT
9211 Hyderabad Station Roard-HS

and i want to select just single line
Purchase_order From_warehouse
003 Hyderabad Station Roard-HSR

not like this
Purchase_order From_warehouse
003 Hyderabad Station Roard-HSR
003 Hyderabad Station Roard-HCT

as you told querry it is shows second result
Purchase_order From_warehouse
003 Hyderabad Station Roard-HSR
003 Hyderabad Station Roard-HCT

ZurdoDev 8-Sep-19 9:12am

Hyderabad Station Roard-HSR is in your data 5 times. Why do you want Purchase Order 003 in your results? Why not 004 or 123?

irfanansari 8-Sep-19 12:27pm

sir actuallay i want to use it as first who create a purchase order its status will be initial then it person who cteared purchase order not complete all stepls and save it and send it to other site mean vender to issue product then it is in intitial status then it will shows in notifiation at side of purchase order who created
step 1 initial
step 2 saved
step 3 sent
step 4 transit for receive
step 5 received/accepted

ZurdoDev 8-Sep-19 17:22pm

I still do not understand exactly what you want. You seem to keep changing what you are saying. It sounds like you want to get the minimum and use group by as the first solution says.

irfanansari 10-Sep-19 17:12pm

sir thanks a lot and sorry actually i was wrong my self i was using double from-ware house double once HSR and same Purchase order number use and change HCT then when i query use of as you gave me it is show double sorry sir again
i am appreciated you
sir if you do not mind may i ask one question if you help in this so actually i am making first time installment application then i have no idea to use logic of inventory as first create a purchase order in tlbpo and its status is sent and then it shows others side mean towarehouse then it will accepted and issue then i want to ask its will works same table or create a new table for it