0

I have a query like this.

select pid,deptid from table1;

in which deptid is varchar and value like (1,3,4)

Now I have another table as department(deptid(int,PK), DeptName)

I want instead of get deptid from select I can get deptNames in csv.like

pid | deptname

1   | dept1,dept4,dept7

2   | dept4,dept9

What should be approach for this.

Uooo
  • 6,204
  • 8
  • 36
  • 63
user952072
  • 107
  • 2
  • 13

2 Answers2

1

Assuming below is your table structure

Departments

deptid  DeptName 
------------
1       HR
2       Accts
3       IT

Employee

pid  deptid
-----------
1    2,1
2    1
3    1,3
4    2,3,1

Try this

SELECT pid, Depts = STUFF((SELECT ',' + d.DeptName 
    FROM @Departments AS d
    INNER JOIN @Employees AS ei
    ON ',' + ei.deptid + ',' LIKE '%,' + CONVERT(VARCHAR(12), d.deptid) + ',%'
    WHERE ei.pid = e.pid
    ORDER BY DeptName
    FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
FROM @Employees AS e
ORDER BY pid;

SQL FIDDLE DEMO

Output

pid Depts
-----------------
1   Accts,HR
2   HR
3   HR,IT
4   Accts,HR,IT
bvr
  • 4,786
  • 1
  • 20
  • 24
0
select t1.pid, t2.DeptName
from table1 t1
join table2 t2 on t1.deptid like concat('%', t2.deptid, '%')

Although one should point out, it is not good practice to store multiple values in a single column like this. If you have control over the table design, you should change it so each column contains just one value. It causes lots of problems, difficulty in joining to another table is just the first problem.

Graham Griffiths
  • 2,196
  • 1
  • 12
  • 15