I have the following SQL query which shows me which Supplier_Part_Numbers have more than one Part_Number listed against them,
SELECT dbo.RemoveNonAlphaCharacters(RTRIM(LTRIM(Supplier_Part_Number)))
, COUNT(a1.Part_Number)
FROM dbo.Supplier_Price_File as a1
GROUP BY dbo.RemoveNonAlphaCharacters(RTRIM(LTRIM(Supplier_Part_Number)))
HAVING COUNT(a1.Part_Number) > 1
The dbo.RemoveNonAlphaCharacters(RTRIM(LTRIM(Supplier_Part_Number))) is standardising all the Supplier_Part_Numbers to find the all duplicate values.
I would like the results to show the part_number which the supplier_part_number is listed against. Something like
SELECT SupplierCode, Supplier_Part_Number
FROM dbo.Supplier_Price_File as a1
WHERE dbo.RemoveNonAlphaCharacters(RTRIM(LTRIM(Supplier_Part_Number))) IN
(
SELECT dbo.RemoveNonAlphaCharacters(RTRIM(LTRIM(Supplier_Part_Number))) AS MyPartNo
-- , COUNT(a1.Part_Number)
FROM dbo.Supplier_Price_File as a1
GROUP BY dbo.RemoveNonAlphaCharacters(RTRIM(LTRIM(Supplier_Part_Number)))
HAVING COUNT(a1.Part_Number) > 1
)
not sure of the exact code (posting DDL and sample data would allow a tested solution ...), but maybe something like this would do
SELECT *
FROM
(
SELECT [MyProductCode] = dbo.RemoveNonAlphaCharacters(RTRIM(LTRIM(Supplier_Part_Number)))
, SupplierCode
, Supplier_Part_Number
, [T_GroupRowCount] = COUNT(*) OVER(PARTITION BY dbo.RemoveNonAlphaCharacters(RTRIM(LTRIM(Supplier_Part_Number))))
FROM dbo.Supplier_Price_File as a1
) AS T
WHERE T_GroupRowCount > 1