如何从同一个表中获取具有相同列的记录通过特定列分成两个不同的列？ [英] how to get the records from same table with same column devided into two different columns by a perticular column?

问题描述

嘿伙计

i有如下表格

表名：tbl_Party

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尝试以下sql查询

i Tried the following sql query

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

但我无法得到预期的结果



所以，我应该触发哪个查询以便我得到上述结果，

thanx ，

but i couldn't get the expected result

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

推荐答案

您假设tbl_Party表中的行将以有序的方式进行。不，它不起作用。这不是关系数据库中的表的设计方式。你应该开始重新设计你的桌子。

根据我得到的小信息，我做了如下快速草案供你参考：

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:

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]

您应该阅读有关关系数据库的更多信息设计 [ ^ ]

在这样的行号上使用JOIN：

Use JOIN on row numbers like this:

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]

这篇关于如何从同一个表中获取具有相同列的记录通过特定列分成两个不同的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！