Consolidate Results

Transact-SQL
1

I can consolidate lines within the source DB however it complicates certain business processes, I would rather consolidate the lines in SQL if possible within the query or after the queries results. I would take the sum for "PD.QTY"

SELECT dbo.PO_PurchaseOrderHeader.PurchaseOrderNo AS PH.NO, dbo.CI_Item.UDF_UNIQUEKEY AS CI.K, dbo.PO_PurchaseOrderDetail.QuantityOrdered AS PD.QTY
FROM dbo.PO_PurchaseOrderHeader AS PH, dbo.CI_Item AS CI, dbo.PO_PurchaseOrderDetail AS PD

SELECT  PH.NO, CI.K, PD.QTY, RIGHT(dbo.CIK, 1) AS SIZE_INDEX, 
                  '' AS [WareHouse Number]
FROM     CI INNER JOIN
                  PD ON CI.ItemCode = PD.ItemCode FULL OUTER JOIN
                  PH ON PD.PurchaseOrderNo = PH.NO
WHERE  (PH.VendorNo = '0001904')
ORDER BY PH.NO

Question on the alias too, do you put everything in one select statement or can you do two like the code i did above?

The bottom result is what I would want.

Example Details.txt File Duplicate Lines Data:
FX34689,1003,10,3
FX34689,1003,10,3
Example Details.txt File Consolidated Lines Data:
FX34689,1003,20,3

2

You can't do what you are asking - you just need a single query:

SELECT PH.NO
     , CI.UDF_UNIQUEKEY AS K
     , PD.QuantityOrdered AS QTY
     , RIGHT(CI.UDF_UNIQUEKEY, 1) AS SIZE_INDEX
  FROM dbo.CI_ITEM AS CI
 INNER JOIN dbo.PO_PurchaseOrderDetail AS PD ON PD.ItemCode = CI.ItemCode
 INNER JOIN dbo.PO_PurchaseOrderHeader AS PH ON PH.NO = PD.PurchaseOrderNo
 WHERE PH.VendorNo = '0001904'
 ORDER BY
       PH.NO;

Since I prefer the alias = column format, this is what that would look like:

SELECT PH.NO
     , K          = CI.UDF_UNIQUEKEY
     , QTY        = PD.QuantityOrdered
     , SIZE_INDEX = RIGHT(CI.UDF_UNIQUEKEY, 1)
  FROM dbo.CI_ITEM AS CI
 INNER JOIN dbo.PO_PurchaseOrderDetail AS PD ON PD.ItemCode = CI.ItemCode
 INNER JOIN dbo.PO_PurchaseOrderHeader AS PH ON PH.NO = PD.PurchaseOrderNo
 WHERE PH.VendorNo = '0001904'
 ORDER BY
       PH.NO;

I also prefer meaningful column aliases - so I would use this:

SELECT OrderNo    = PH.NO
     , UniqueKey  = CI.UDF_UNIQUEKEY
     , Quantity   = PD.QuantityOrdered
     , SizeIndex  = RIGHT(CI.UDF_UNIQUEKEY, 1)
  FROM dbo.CI_ITEM                     AS CI
 INNER JOIN dbo.PO_PurchaseOrderDetail AS PD ON PD.ItemCode = CI.ItemCode
 INNER JOIN dbo.PO_PurchaseOrderHeader AS PH ON PH.NO = PD.PurchaseOrderNo
 WHERE PH.VendorNo = '0001904'
 ORDER BY
       PH.NO;