SQL: Duplicate columns from join query
I have a table purchase which is
purchase_id distributor_code address
1 12 xyz
2 13 abc
another table purchase_item which has
purchase_id item_name
1 car
1 bike
i want to make a bill of item in which info from purchase table and purchase_item table should be shown for a given purchase_id... Required:::
purchase_id=1,distributor_code=12,address=xyz,itemname=car,bike....
in my query distributor_code and address are getting repeated twice a开发者_高级运维s there are 2 itemnames in purchase_item table for purchase_id=1...i have tried using outer join...please help!
Select P.purchase_id
, P.distributor_code
, P.address
, Stuff(
(
Select ', ' + Item.item_name
From purchase_item As Item
Where Item.purchase_id = P.purchase_id
Order By Item.item_name
For Xml Path(''), type
).value('.', 'nvarchar(max)'), 1, 2, '') As Items
From purchase As P
精彩评论