开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜