Thanks for that. I should have tried it myself first:
Here goes the working syntax for the table:
USE [BRI DW]
GO
/****** Object: Table [dbo].[FreightContents] Script Date: 02/19/2013 16:21:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Create TABLE [dbo].[FreightContents](
[HeaderKey] [char](35) NOT NULL,
[PurchaseOrderNumber] [char](30) NOT NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO FreightContents VALUES('FJ#1000210','104001-00-3')
INSERT INTO FreightContents VALUES('FJ#1000211','SAMPLE' )
INSERT INTO FreightContents VALUES('FJ#1000211','Hydraulic Cylinder' )
INSERT INTO FreightContents VALUES('FJ#1000211','99148/10182' )
INSERT INTO FreightContents VALUES('FJ#1000211','99148/101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','99148' )
INSERT INTO FreightContents VALUES('FJ#1000211','99148' )
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101913' )
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101185' )
INSERT INTO FreightContents VALUES('FJ#1000211','101186' )
INSERT INTO FreightContents VALUES('FJ#1000211','101187' )
INSERT INTO FreightContents VALUES('FJ#1000211','101188' )
INSERT INTO FreightContents VALUES('FJ#1000211','101189' )
INSERT INTO FreightContents VALUES('FJ#1000211','101110' )
INSERT INTO FreightContents VALUES('FJ#1000211','101111' )
INSERT INTO FreightContents VALUES('FJ#1000211','101112' )
INSERT INTO FreightContents VALUES('FJ#1000211','101113' )
INSERT INTO FreightContents VALUES('FJ#1000211','101181' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182')
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101914' )
INSERT INTO FreightContents VALUES('FJ#1000211','101185' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','101186' )
INSERT INTO FreightContents VALUES('FJ#1000211','101182' )
INSERT INTO FreightContents VALUES('FJ#1000211','101187' )
INSERT INTO FreightContents VALUES('FJ#1000211','101186' )
INSERT INTO FreightContents VALUES('FJ#1000211','101185' )
INSERT INTO FreightContents VALUES('FJ#1000211','101184' )
INSERT INTO FreightContents VALUES('FJ#1000211','101183' )
Here goes the working CTE:
USE [Database2]
GO
/****** Object: View [dbo].[CTE_POList_UK] Script Date: 02/19/2013 16:26:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CTE_POList_UK] as
WITH CTE ( HeaderKey, PO_list, product_name, length )
AS ( SELECT HeaderKey, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
FROM Database1..FreightContents with(readuncommitted)
GROUP BY HeaderKey
UNION ALL
SELECT p.HeaderKey, CAST( PO_list +
CASE WHEN length = 0 THEN '' ELSE ', ' END + rtrim(PurchaseOrderNumber) AS VARCHAR(8000) ),
CAST( PurchaseOrderNumber AS VARCHAR(8000)), length + 1
FROM CTE c
INNER JOIN Database1..FreightContents p with(readuncommitted)
ON c.HeaderKey = p.HeaderKey
WHERE p.PurchaseOrderNumber > c.product_name )
SELECT HeaderKey, PO_list
FROM ( SELECT Distinct substring(HeaderKey,4,10), PO_list,
RANK() OVER ( PARTITION BY HeaderKey ORDER BY length DESC )
FROM CTE ) D ( HeaderKey, PO_list, rank )
WHERE rank = 1 ;
GO
------------------------------
Once created it shows the results upon right clicking the query in object explorer and selecting