SELECT t.orderno, 
COALESCE(t.ShiptoAddress,t1.ShipToAddress) AS ShiptoAddress,
other columns....
FROM Table t
OUTER APPLY (SELECT TOP 1 ShipToAddress
             FROM Table
             WHERE orderno= t.orderno
             )t1

quote:

---

Originally posted by theHydra

Ran exactly as you posted, but got a 'incorrect syntax near the keyword TABLE' in the OUTER APPLY section.

I changed the FROM Table to ARMAST, but after that I get a

'SQL Server Database Error: There is already an object named '#Temp' in the database.'

error.

Any ideas?

---

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
 DROP TABLE #Temp
SELECT
case when dbo.aritem.fpartno = 'SHIPPING' then 'Y' else '' end AS Shipping
,dbo.armast.fcstatus AS Status
,dbo.armast.fcinvoice AS Invoice
,dbo.armast.finvdate AS Invoice_Date
,dbo.armast.fcustno AS Cust_Number
,dbo.armast.fbcompany AS Bill_To_Company
,dbo.armast.fshipdate AS Ship_Date
,dbo.armast.fsono AS SO_Number
,dbo.aritem.fpartno AS Part_No
,dbo.aritem.ftotprice AS Total_Price
,dbo.aritem.fitem AS Line_Item
,dbo.shmast.fccity AS Ship_To_City
,dbo.shmast.fcstate AS Ship_To_State
,dbo.shmast.fccountry AS Ship_To_Country
,dbo.shmast.fczip AS Ship_To_Zip
,dbo.shmast.fshipno AS Shipper INTO #Temp
FROM
dbo.armast
INNER JOIN
dbo.aritem
ON dbo.armast.fcinvoice = dbo.aritem.fcinvoice
LEFT OUTER JOIN
dbo.shitem
ON dbo.aritem.fshipkey = (shitem.fshipno + shitem.fitemno)
LEFT OUTER JOIN dbo.shmast
ON dbo.shmast.fshipno = shitem.fshipno
where armast.finvdate > = DATEADD(dd, 0, DATEDIFF(dd, 15, GETDATE()))

SELECT Shipping,Status,Invoice,Invoice_Date,Cust_Number,Bill_To_Company,Ship_Date,SO_Number,Part_No,Total_Price,Line_Item,
COALESCE(t1.Ship_To_City,t2.Ship_To_City) AS Ship_To_City,
COALESCE(t1.Ship_To_State,t2.Ship_To_State) AS Ship_To_State,
COALESCE(t1.Ship_To_Country,t2.Ship_To_Country) AS Ship_To_Country,
COALESCE(t1.Ship_To_Zip,t2.Ship_To_Zip) AS Ship_To_Zip,
Shipper 
FROM #Temp t1
OUTER APPLY (SELECT TOP 1 Ship_To_City,Ship_To_State,Ship_To_Country,Ship_To_Zip
             FROM Table
             WHERE fcinvoice = t1.fcinvoice 
             )t2
ORDER BY t1.fcinvoice ASC

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #Temp
SELECT
dbo.armast.fcstatus AS Status
,dbo.armast.fcinvoice AS Invoice
,dbo.armast.finvdate AS Invoice_Date
,dbo.armast.fcustno AS Cust_Number
,dbo.armast.fbcompany AS Bill_To_Company
,dbo.armast.fshipdate AS Ship_Date
,dbo.armast.fsono AS SO_Number
,dbo.aritem.fpartno AS Part_No
,dbo.aritem.ftotprice AS Total_Price
,dbo.aritem.fitem AS Line_Item
,dbo.shmast.fccity AS Ship_To_City
,dbo.shmast.fcstate AS Ship_To_State
,dbo.shmast.fccountry AS Ship_To_Country
,dbo.shmast.fczip AS Ship_To_Zip
,dbo.shmast.fshipno AS Shipper INTO #Temp
FROM
dbo.armast
INNER JOIN
dbo.aritem
ON dbo.armast.fcinvoice = dbo.aritem.fcinvoice
LEFT OUTER JOIN
dbo.shitem
ON dbo.aritem.fshipkey = (shitem.fshipno + shitem.fitemno)
LEFT OUTER JOIN dbo.shmast
ON dbo.shmast.fshipno = shitem.fshipno
where armast.finvdate > = DATEADD(dd, 0, DATEDIFF(dd, 15, GETDATE()))

SELECT Shipper,Status,Invoice,Invoice_Date,Cust_Number,Bill_To_Company,Ship_Date,SO_Number,Part_No,Total_Price,Line_Item,
COALESCE(t1.Ship_To_City,t2.Ship_To_City) AS Ship_To_City,
COALESCE(t1.Ship_To_State,t2.Ship_To_State) AS Ship_To_State,
COALESCE(t1.Ship_To_Country,t2.Ship_To_Country) AS Ship_To_Country,
COALESCE(t1.Ship_To_Zip,t2.Ship_To_Zip) AS Ship_To_Zip,
Shipper 
FROM #Temp t1
OUTER APPLY (SELECT TOP 1 Ship_To_City,Ship_To_State,Ship_To_Country,Ship_To_Zip
FROM #Temp
WHERE invoice = t1.invoice 
)t2
ORDER BY t1.invoice ASC

SELECT Invoice
FROM #Temp
GROUP BY Invoice
HAVING SUM(CASE WHEN Ship_To_City IS NOT NULL THEN 1 ELSE 0 END) =0