modify SQL output - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

modify SQL output

Author

Topic

wibni
Starting Member

31 Posts

Posted - 2015-01-08 : 03:37:51

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

nagino
Yak Posting Veteran

75 Posts

Posted - 2015-01-13 : 19:39:50

Like Following?

WITH POTable ([PO#], [PO Date], [PO Line #], [Item #], Quantity) AS (
SELECT 'PO1', '20150101', 1, 'Item 1', 1
UNION ALL
SELECT 'PO1', '20150101', 2, 'Item 2', 20
), ShippingTable ([PO#], [ETA Date], [Line #], Waybill, [Ship Quantity]) AS (
SELECT 'PO1', '20150130', 1, 'abc', 1
UNION ALL
SELECT 'PO1', '20150130', 2, 'abc', 15
UNION ALL
SELECT 'PO1', '20150215', 2, 'def', 5
)

SELECT
	POTable.[PO#],
	POTable.[PO Date],
	POTable.[PO Line #],
	POTable.[Item #],
	POTable.Quantity,
	ShippingTable.[ETA Date],
	ShippingTable.Waybill,
	ShippingTable.[Ship Quantity]
FROM POTable
RIGHT JOIN ShippingTable
	ON POTable.[PO#] = ShippingTable.[PO#]
	AND POTable.[PO Line #] = ShippingTable.[Line #]

-------------------------------------
From Japan
Sorry, my English ability is limited.

wibni
Starting Member

31 Posts

Posted - 2015-01-19 : 03:55:07

Hi nagino,

Thanks for your reply, but it does not what I was after.
Have done it now with Crystal reports. Was just thinking there is a way to do it in SQL as well.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-19 : 06:34:02

it can be done its equal but doing it in Crystal Reports Or ssrs is actually probably the better way

wibni
Starting Member

31 Posts

Posted - 2015-01-19 : 19:42:53

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources