• 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