Append string to field in first row of result set

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 2008 Forums

Transact-SQL (2008)

Append string to field in first row of result set

Author

Topic

rlull
Starting Member

39 Posts

Posted - 2013-11-11 : 11:45:46

I have a simple query:

SELECT c.CategoryID, c.CategoryDisplayName, c.CategoryParentID, c.CategoryOrder
FROM CMS_Category c
WHERE c.CategoryID='22' OR c.CategoryParentID='22'
ORDER BY c.CategoryParentID, c.CategoryOrder ASC

In this case there are 8 rows returned. Only one row has a CategoryID of 22 (this is the parent). The remaining 7 rows have a CategoryParentID of 22. On the parent row where CategoryID = 22, I need to append a string to the value returned from the CategoryDisplayName field. How do I do this?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-11 : 11:49:58

Do you mean this?

SELECT  c.CategoryID ,
        c.CategoryDisplayName 
			+ CASE WHEN c.CategoryID = '22' THEN 'AppendedString' ELSE '' END as CategoryDisplayName ,
        c.CategoryParentID ,
        c.CategoryOrder
FROM    CMS_Category c
WHERE   c.CategoryID = '22'
        OR c.CategoryParentID = '22'
ORDER BY c.CategoryParentID ,
        c.CategoryOrder ASC

rlull
Starting Member

39 Posts

Posted - 2013-11-11 : 11:56:17

Thanks! That was almost perfect. I modified it as follows to achieve what I was after:

SELECT 'DocumentCategoryIDs',
c.CategoryID,
CASE WHEN c.CategoryID = '22' THEN c.CategoryDisplayName + ' All' ELSE c.CategoryDisplayName END as CategoryDisplayName,
c.CategoryParentID,
c.CategoryOrder
FROM CMS_Category c
WHERE c.CategoryID='22' OR c.CategoryParentID='22'
ORDER BY c.CategoryParentID, c.CategoryOrder ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources