-1

I have a table, which has a field "SendPDF" varchar(max). In that column I have values like 

3065-Employee,1206-Company Admin
3065-Employee
1206-Company Admin,265-Employee,1324-Company Admin
........

3065-Employee this means 3065 is Id of the employee, 1206 is Id of Company Admin and same for the other records. Now what I want is I need to get the names on the basis of their usertype (i.e. Employee or either Company Admin). So the result from above records would be

Ajay Sharma, Mahesh Singh
Ajay Sharma
Mahesh Singh, Rajesh Tiwari, Ramesh Vyas
....

How can I prepare a query?

halfer
  • 19,824
  • 17
  • 99
  • 186
nrsharma
  • 2,532
  • 3
  • 20
  • 36
  • can you please post the output – mohan111 Mar 16 '15 at 10:26
  • @mohan111, I have edited the question for the desired result. – nrsharma Mar 16 '15 at 10:29
  • 2
    Normalise you database first: that scheme for storing the ids is why you have this problem. – David Soussan Mar 16 '15 at 10:31
  • I agree with you @DavidSoussan, but right now I can't change that scheme, can you please suggest me anysolution to get the records in desired output format? – nrsharma Mar 16 '15 at 10:33
  • Are `Employees` and `Company Admins` stored in two different Tables? – xpy Mar 16 '15 at 10:45
  • @xpy Yes, they are in different tables. – nrsharma Mar 16 '15 at 10:46
  • Hi nrsharma. It is quite normal for posts to be edited here. Posts that are chatty or feature boilerplate of hello...please help me... thanx in advance...much appreciated...regards... (etc) are often subject to editing. If your post is edited, don't worry about it. Please do not roll them back. I will reinstate my edit, since it is correct (see my ~26K edits). If you feel strongly about rolling back, please ping me so we can involve a moderator. – halfer May 02 '17 at 13:01

2 Answers2

1

You will have to split the string by comma ',' and then make your join.

Splitting strings by specific char in SQL Server cannot be done natively, you'll have to use a User Defined Function, there are many examples on the internet but here is one from Stackoverflow to get you started with.

You can use it like this: 

    WITH tmp AS (
        SELECT * FROM Split(SendPDF,',')
    ) SELECT name FROM employees JOIN tmp2 ON tmp.Item = CAST(employees.id AS VARCHAR)+'-Employee' 
    UNION
    SELECT name FROM CompanyAdmins JOIN tmp2 ON tmp.Item = CAST(CompanyAdmins.id AS VARCHAR)+'-Company Admin'

This is not an elegant solution, it is just something you can do to have your job done. You should normalize your data as others said.

Community
  • 1
  • 1
xpy
  • 5,481
  • 3
  • 29
  • 48
0

You should not be storing such lists in a comma-separated fields. These should be in a junction table with multiple columns, one for the employee id and one for the job title. SQL has a great data type for storing lists of things. It is called a table not a string.

Sometimes, we are stuck with other peoples bad design decisions. You should try to get this fixed. Assuming you have a table, say employees, with one row per employee id and nam, you can do something like:

select bt.*, e.employeename
from brokentable bt join
     employees e
     on ',' + bt.sendPDF like '%,' + cast(e.employeeid as varchar(max)) + '-';

This produces one row per employee. You should get away from comma-delimited lists, so that seems sufficient for now.

gvee
  • 16,732
  • 35
  • 50
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786