modify SQL output

• boettger.andreas

  SSC Eights!

  Points: 965

  More actions

  January 8, 2015 at 1:47 am

  #145650

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 8, 2015 at 1:55 am

  #1768697

  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