InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 10:04:48 GMT20http://www.sqlservercentral.com/Forums/Topic1421675-338-1.aspxThanks Indeed, This works perfectly regardless of the No. of rows in FreightContents. :-) Good dayWed, 20 Feb 2013 03:35:02 GMTsonia.luthrahttp://www.sqlservercentral.com/Forums/Topic1421675-338-1.aspxI still could not run your CTE due to type mismatch error. (I guess when you do CHAR concatenation in the recursive CTE you should cast to NVARCHAR). However, looks like your solution will not perform good enough anyway.Try this instead:[code="sql"]SELECT HeaderKey ,STUFF((SELECT ', ' + RTRIM(CAST(f2.PurchaseOrderNumber AS VARCHAR(30))) FROM FreightContents AS f2 WHERE f2.HeaderKey = f1.HeaderKey ORDER BY PurchaseOrderNumber FOR XML PATH ('')), 1, 2, '') AS PO_listFROM FreightContents AS f1GROUP BY HeaderKey[/code]Wed, 20 Feb 2013 03:27:25 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1421675-338-1.aspxThanks 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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCreate TABLE [dbo].[FreightContents]( [HeaderKey] [char](35) NOT NULL, [PurchaseOrderNumber] [char](30) NOT NULL, ) ON [PRIMARY] GOSET ANSI_PADDING OFFGOINSERT 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[CTE_POList_UK] asWITH 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 Tue, 19 Feb 2013 09:24:58 GMTsonia.luthrahttp://www.sqlservercentral.com/Forums/Topic1421675-338-1.aspxOne of the reason you don't have much of answers is that your posted code cannot be executed without error... [code="sql"]INSERT INTO FreightContents VALUES('FJ#1000210','104001-00-3') ...[/code]results with error:[b]Msg 213, Level 16, State 1, Line 1Column name or number of supplied values does not match table definition.[/b]So, which columns the above data should be inserted into?Also, runnable query with CTE would help more than just some cut out of it.Tue, 19 Feb 2013 09:14:56 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1421675-338-1.aspxHi AllI am using a CTE query for recording a list of purchase order numbers separated by a comma for a HeaderKey under one column as below:---------------------------------HeaderKey PO_list1000215 101142-00-36, 101820-00-37, 103643-00-37, 104120-00-37, 104530-00-37, 105095-00-37, 105700-00-37, 5062 SD, 99273-00-37, FIAT SAMPLES, S.RAPSON1000219 102288, 1045451000220 104321-00-3, 104321-00-4, 104321-00-5, 105715-00-4, 105715-00-5, 105715-003I have run into the problem of excessive throughput time when the list of purchase orders has more than 15 entries listed in the Freightcontents table. I hope someone has a solution for this issue. The table definition from where I am reading the list is:---------------------------CREATE TABLE [dbo].[FreightContents]( [Description] [char](35) NOT NULL, [DetailKey] [char](35) NOT NULL, [HeaderKey] [char](35) NOT NULL, [ProductCode] [char](20) NOT NULL, [PurchaseOrderNumber] [char](30) NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFFGO----------------------------The data looks like below:-------------------------------------------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' )The CTE definition is :------------------------SELECT HeaderKey, CAST('' AS VARCHAR(8000)), CAST('' AS VARCHAR(8000)), 0FROM Database1..FreightContents WITH (readuncommitted)GROUP BY HeaderKeyUNION ALLSELECT p.HeaderKey, CAST(PO_list + CASE WHEN length = 0 THEN '' ELSE ', ' END + rtrim(PurchaseOrderNumber) AS VARCHAR(8000)), CAST(PurchaseOrderNumber AS VARCHAR(8000)), length + 1FROM CTE c INNER JOIN Database1..FreightContents p WITH (readuncommitted) ON c.HeaderKey = p.HeaderKeyWHERE 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;Tue, 19 Feb 2013 08:29:24 GMTsonia.luthra