how to get the records from same table with same column devided into two different columns by a perticular column?

Question

1.00/5 (1 vote)

See more:

hey guys
i have table like below
Table Name : tbl_Party

     Name       Type     Amount
---------------------------------
    person1     saler    3500      
    person2     saler    200       
    person3     buyer    1500      
    person4     buyer    300       
    person5     saler    4500      
    person6     saler    3000

and i need the result like below

   Saler_Name     S_Amount   Buyer_Name  B_Amount
--------------------------------------------------
    person1       3500       person3       1500
    person2       200        person4       300
    person5       4500        null         null
    person6       3000        null         null

i Tried the following sql query

SQL

SELECT  p1.Name as Saler_Name,p1.Amount as S_Amount ,p2.Name Buyer_Name,p2.Amount as B_Amount FROM [dbo].[tbl_Party] p1 RIGHT JOIN [dbo].[tbl_Party] p2 ON p1.Type=p2.Type

but i couldn't get the expected result

so,which query i should fire so that i get the above result,
thanx,

Posted 4-Mar-14 20:16pm

patelpratik

Updated 7-Mar-14 17:30pm

v4

Add a Solution

Comments

Maciej Los 5-Mar-14 14:11pm

Based on what condition? There is no relation between persons as salers and buyers.

patelpratik 7-Mar-14 23:36pm

query should return 4 columns..
the condition is on column 'Type'
if Type = saler than display column 'Name','Amount' as Saler_Name and S_amount
if Type = buyer than display column 'Name','Amount' as Buyer_Name and B_amount
as displayed in above result....
i Tried but failure..failure..and failure

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)

Peter Leow · Answer 1 · 2014-03-07T19:29:00

You are assuming that the rows in the tbl_Party table will be in that orderly manner. No, it does not work that way. That is not how the tables in a relational datebase are designed. You should start re-designing your table(s).
Based on the little information that I got, I did a quick draft as follows for your reference:

VB

table: patron
patron_id (primary key)
patron_name
[other fields]

table: seller
sale_id (primary key, foreign key to sale_id in sales table)
seller_id (primary key, foreign key to patron_id in sales table)
[other fields]

table: buyer
sale_id (primary key, foreign key to sale_id in sales table)
buyer_id (primary key, foreign key to patron_id in sales table)
[other fields]

table: sales
sale_id (primary key)
asking_price
offered_price
concluded_price
[other fields]

You should read more on Relational Database Design[^]

Sergey Vaselenko · Answer 2 · 2014-03-07T20:14:00

Solution 2

Use JOIN on row numbers like this:

SQL

SELECT 
	*
FROM
	(
	SELECT Name, Amount, RowID = ROW_NUMBER() OVER (ORDER BY Name) FROM [dbo].[tbl_Party] WHERE [Type] = 'saler'
	) p1
	LEFT OUTER JOIN (
	SELECT Name, Amount, RowID = ROW_NUMBER() OVER (ORDER BY Name) FROM [dbo].[tbl_Party] WHERE [Type] = 'buyer'
	) p2 ON p1.[RowID] = p2.[RowID]

Posted 7-Mar-14 20:14pm

Sergey Vaselenko

Updated 7-Mar-14 20:22pm

v4

Comments

patelpratik 8-Mar-14 10:37am

Tried but give an error at "ROW_NUMBER()"

Sergey Vaselenko 8-Mar-14 11:16am

What version of SQL Server do you use?
SQL Server 2005 has this function.
http://technet.microsoft.com/en-us/library/ms186734(v=sql.90).aspx

patelpratik 8-Mar-14 22:55pm

I am not using sql server....
i m using mdb file
But i fatch record using sql query

Sergey Vaselenko 9-Mar-14 5:27am

I can't help with Access SQL. You may change the SQL Server tag to Access to get the help of Access developers.