Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Help with concatenating

Author  Topic  drdre Starting Member 3 Posts Posted - 2011-08-03 : 13:25:55 WITH Ranked ( Itemcode, ShortDescription, USERFIELD1, USERFIELD2 ,USERFIELD3,USERFIELD4, ItemStatusDesc, rnk, ItemDesc, ItemContent) AS ( SELECT Itemcode , RSITEM.ItemDesc As ShortDescription , USERFIELD1 , USERFIELD2 , USERFIELD3 , USERFIELD4 , ItemStatusDesc , ROW_NUMBER() OVER( PARTITION BY Itemcode ORDER BY Itemcode ) , CAST( rssqldb.dbo.RSITEMDESCRIPTION.ItemDesc AS VARCHAR(8000)) , CAST( rssqldb.dbo.RSITEMCONTENT.ItemContent AS VARCHAR(8000)) FROM rssqldb.dbo.RSITEM INNER JOIN rssqldb.dbo.RSITEMDESCRIPTION ON rssqldb.dbo.RSITEM.ItemID = rssqldb.dbo.RSITEMDESCRIPTION.ItemID INNER JOIN rssqldb.dbo.RSITEMCONTENT ON rssqldb.dbo.RSITEM.ItemID = rssqldb.dbo.RSITEMCONTENT.ItemID INNER JOIN rssqldb.dbo.RSACCOUNT ON rssqldb.dbo.RSITEM.AccountID = rssqldb.dbo.RSACCOUNT.AccountID INNER JOIN rssqldb.dbo.RSITEMSTATUS ON rssqldb.dbo.RSITEM.ItemStatusID= rssqldb.dbo.RSITEMSTATUS.ItemStatusID Where rssqldb.dbo.RSITEM.ITEMCode='0000798' ) , AnchorRanked ( Itemcode,ShortDescription, USERFIELD1, USERFIELD2,USERFIELD3,USERFIELD4,ItemStatusDesc, rnk, ItemDesc, ItemContent ) AS ( SELECT Itemcode , ShortDescription , USERFIELD1 , USERFIELD2 , USERFIELD3 , USERFIELD4 , ItemStatusDesc , rnk , ItemDesc , ItemContent FROM Ranked WHERE rnk = 1 ) , RecurRanked ( Itemcode,ShortDescription, Userfield1,USERFIELD2,USERFIELD3,USERFIELD4,ItemSt atusDesc, rnk, ItemDesc, ItemContent ) AS ( SELECT Itemcode , ShortDescription , USERFIELD1 , USERFIELD2 , USERFIELD3 , USERFIELD4 , ItemStatusDesc , rnk , ItemDesc , ItemContent FROM AnchorRanked UNION ALL SELECT Ranked.Itemcode , RANKED.ShortDescription , RANKED.USERFIELD1 , RANKED.USERFIELD2 , RANKED.USERFIELD3 , RANKED.USERFIELD4 , RANKED.ItemStatusDesc , Ranked.rnk , RecurRanked.ItemDesc + ', ' + Ranked.ItemDesc , RecurRanked.ItemContent + ', ' + Ranked.ItemContent FROM Ranked INNER JOIN RecurRanked ON Ranked.Itemcode = RecurRanked.Itemcode AND Ranked.rnk = RecurRanked.rnk + 1 ) SELECT Itemcode , ShortDescription , MAX( ItemDesc ) As LongDescription , MAX(ItemContent) As Content , UserField1 , USERFIELD2 , USERFIELD3 , USERFIELD4 , ItemStatusDesc As ItemStatus FROM RecurRanked GROUP BY Itemcode,ShortDescription, UserField1, USERFIELD2,USERFIELD3,USERFIELD4, ItemStatusDesc; The above is the coding that i have done.. but i run into a problem when the ItemDesc field is less populated than the Content field and vise versa. It would duplicate the data in the field till both ItemDesc and ItemContent are the same in length. Any suggestions on how i can correct this. With Jah Jah anything is possible