Convert IDs to Values - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Convert IDs to Values

Author

Topic

giteshshah
Starting Member

5 Posts

Posted - 2013-11-28 : 22:28:42

Hi Guys,

I have got 2 tables:
User Table
UserID Email PublicationIDs
--------------------------
1 HR@test.com 11254|11255|11256|
2 Accts@test.com 11254|11256|
3 IT@test.com 11254|

Publication Table
PublicationID Name
-------------------
11254 Clinical
11255 Dermatology
11256 Dental

Now I want an output as below:

Email PublicationNames
1 Clinical|Dermatology|Dental|
2 Clinical|Dental|
3 Clinical|

Any ideas how to achieve the above results?

I have tried a condition like this, but it gives me an error:
SELECT usr.Email,usr.FirstName, GROUP_CONCAT(pub.SubCategoryName)
FROM dbo.CMS_User usr
INNER JOIN dbo.RR_PublicationSubCategories pub
ON FIND_IN_SET(pub.PublicationSubCategoriesID, usr.PublicationsNZ) > 0
GROUP BY usr.Email, usr.FirstName

Gives me an error msg:
'GROUP_CONCAT' is not a recognized built-in function name.

Your help is appreciated.

Thanks
Gitesh Shah

Gitesh Shah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-28 : 23:27:21

Answered here
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/74badea9-17b8-47a7-b762-3e69e51b3957/convert-ids-to-values?forum=transactsql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources