modify SQL output

  • Hi,

    I have a PO table and a shipping details table which I've joined.

    A line item on a PO can be shipped with multiple shipments.

    I want to be able to return the PO line item details only once, but show all details from the shipping table which are related to this line item.

    My Data is like this:

    PO table:

    PO# | PO Date | PO Line # | Item # | Quantity

    PO1 | 20150101 | 1 | Item 1 | 1

    PO1 | 20150101 | 2 | Item 2 | 20

    Shipping table

    PO# | ETA Date | Line # | Waybill | Ship Quantity

    PO1 | 20150130 | 1 | abc | 1

    PO1 | 20150130 | 2 | abc | 15

    PO1 | 20150215 | 2 | def | 5

    The result I'm after is something like this.

    PO# | PO Date | PO Line # | Item # | Quantity | ETA Date | Waybill | Ship Quantity

    PO1 | 20150101 | 1 | Item 1 | 1 | 20150130 | abc | 1

    PO1 | 20150101 | 2 | Item 2 | 20 | 20150130 | abc | 15

    NULL| NULL | NULL | NULL | NULL | 20150215 | def | 5

    My query gives me this.

    The details in red is what I want to hide.

    Can this be done in SQL?

    I'm currently doing it in Crystal reports with a subreport, but was hoping I can do it in SQL.

    PO# | PO Date | PO Line # | Item # | Quantity | ETA Date | Waybill | Ship Quantity

    PO1 | 20150101 | 1 | Item 1 | 1 | 20150130 | abc | 1

    PO1 | 20150101 | 2 | Item 2 | 20 | 20150130 | abc | 15

    PO1 | 20150101 | 2 | Item 2 | 20 | 20150215 | def | 5

    Thanks for any suggestions

  • That's data formatting, Crystal Reports is completely the correct place to do that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply