February 7, 2012 at 1:44 pm
There is a table which has:
PersonName
PersonNameType
transaction#
PersonNameType either = buyer or = seller
Each transcation# list twice. bacause the record need to link with "Buyer" one time and "Seller" second time
Example
PersonName PersonNameType Transaction #
John buyer 00001
Mary Seller 00001
How can I write a qury to get the result as below:
Transaction # Buyer' Name Seller's Name
00001 John Mary
Thanks!
February 7, 2012 at 1:50 pm
This should help you start figuring it out.
SELECT a.Personname AS Buyer, b.PersonName AS Seller,a.transactionnum
FROM sometable a
INNER JOIN sometable b
ON a.transactionnum = b.transactionnum
WHERE a.personnametype = 'buyer'
AND b.personnametype = 'seller'
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2012 at 2:34 pm
You can also do it this way:
SELECT [Buyer],[Seller], TransactionNumber
FROM (
SELECT PersonName,
PersonNameType,
TransactionNumber
FROM Transactions
) AS B
PIVOT (
MIN( PersonName )
FOR PersonNameType IN ( [Buyer], [Seller] )
) AS P
You get one less scan of the table, but I wouldn't do it if you don't know that you have good data.
February 7, 2012 at 3:28 pm
CELKO (2/7/2012)
This is minimal polite behavior on a SQL forum.
Seriously? Would you like to be the pot, or the kettle, for the remainder of that conversation?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 8, 2012 at 8:48 am
Thanks a lot the helps, even with addtional choice!!!!
February 8, 2012 at 8:57 am
Evil Kraig F (2/7/2012)
CELKO (2/7/2012)
This is minimal polite behavior on a SQL forum.Seriously? Would you like to be the pot, or the kettle, for the remainder of that conversation?
Craig! :laugh:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply