Coalesce Ordering. Possible? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Coalesce Ordering. Possible?

Author

Topic

msch-prv
Starting Member

11 Posts

Posted - 2011-03-17 : 06:31:57

Hi, I just wonder if it is possible to order data within a coalesce expression.

Specifically, in the below query I would like to order the expression by Path then by BusCatDesc_Cult. Unfortunately, I cannot join table vid_BusCatID_Cult to table @tree because vid_BusCatID_Cult.BusCatID is multi-valued (distinct record for every CultCode).

TIA for sharing any tips.

SELECT @csv = COALESCE(@csv,'') +	
   CONVERT(varchar(5),t.BusCatID) + '~' + 
   (SELECT BusCatDesc_Cult FROM  vid_BusCatID_Cult 
      WHERE (BusCatID = t.BusCatID) AND (CultCode = @CultCode)) + '~' +
   CONVERT(varchar(5),tbl.ParentID) + '~' + 
   CONVERT(varchar(5),lvl) + ';'

FROM @tree t INNER JOIN 
   vid_BusCat tbl ON t.BusCatID = tbl.BusCatID 
ORDER BY Path;

msch-prv
Starting Member

11 Posts

Posted - 2011-03-17 : 06:54:38

Well, it seems after some more experimenting that the following works. Sorry!

SELECT @csv = COALESCE(@csv,'') +	
   CONVERT(varchar(5),t.BusCatID)  + '~' +  
   BusCatDesc_Cult + '~' + 
   CONVERT(varchar(5),tbl.ParentID) + '~' + 
   CONVERT(varchar(5),lvl) + ';'
FROM @tree t INNER JOIN 
   vid_BusCat tbl ON t.BusCatID = tbl.BusCatID INNER JOIN 
      vid_BusCatID_Cult ON t.BusCatID = vid_BusCatID_Cult.BusCatID WHERE CultCode = @CultCode
ORDER BY Path, BusCatDesc_Cult;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources