I have 2 tables
Departments
ID Dept
---------
1 HR
2 Accts
3 IT
Employee
ID Name Depts
-------------------
1 Kevin 2,1
2 Michelle 1
3 Troy 1,3
4 Rheesa 2,3,1
I am looking for an output like the following with a SQL query.
Employee depts
ID Name Depts
-------------------------
1 Kevin Accts,HR
2 Michelle HR
3 Troy HR,IT
4 Rheesa Accts,IT,HR
I have tried the following that join s with depts but results in one row for each dept only. How do i get the above results using a query?
select
name, depts, dept
from
employee
CROSS APPLY
dbo.split_list(employee.depts ,',') split
inner join
dbo.department on depts= split.value
order by
name
In order to fetch the comma separated (delimited) values from the Stored Procedure, you need to make use of a variable with data type and size same as the Output parameter and pass it as Output parameter using OUTPUT keyword.
Approach: This can be achieved with the help of join() method of String as follows. Get the List of String. Form a comma separated String from the List of String using join() method by passing comma ', ' and the list as parameters. Print the String.
DECLARE @Departments TABLE
(
ID INT PRIMARY KEY,
Dept VARCHAR(32) NOT NULL UNIQUE
);
DECLARE @Employees TABLE
(
ID INT PRIMARY KEY,
Name NVARCHAR(64) NOT NULL,
Depts VARCHAR(255) NOT NULL
);
INSERT @Departments VALUES
(1,'HR'), (2,'Accts'), (3,'IT');
INSERT @Employees VALUES
(1,'Kevin','2,1'), (2,'Michelle','1'),
(3,'Troy','1,3'), (4,'Rheesa','2,3,1');
SELECT ID, Name, Depts = STUFF((SELECT ',' + d.Dept
FROM @Departments AS d
INNER JOIN @Employees AS ei
ON ',' + ei.Depts + ',' LIKE '%,' + CONVERT(VARCHAR(12), d.id) + ',%'
WHERE ei.ID = e.ID
ORDER BY Dept
FOR XML PATH,
TYPE).value(N'/text().[1]', N'nvarchar(max)'), 1, 1, N'')
FROM @Employees AS e
ORDER BY ID;
The results don't quite match your required results, as the ordering is deterministic (ordered by department name):
ID Name Depts
---- -------- ----
1 Kevin Accts,HR
2 Michelle HR
3 Troy HR,IT
4 Rheesa Accts,HR,IT
If you want them ordered by the appearance in the comma-separated list, just change:
ORDER BY Dept
To:
ORDER BY CHARINDEX( ',' + CONVERT(VARCHAR(12), d.id) + ',', ',' + ei.Depts + ',')
Results:
ID Name Depts
---- -------- ----
1 Kevin Accts,HR
2 Michelle HR
3 Troy HR,IT
4 Rheesa Accts,IT,HR -- this is the only one affected as it turns out
However, in reality, you should normalize your database. This is an absolute nightmare.
Looking beyond how you're storing your data, let me try to help you out.
Well, you're asking a lot of questions here. First, to split the data, you can format it as XML and use CROSS APPLY -- trick I saw a while back that didn't require built in functions.
That will convert your comma delimited string to a list of strings. You can then use FOR XML to put them back together.
Give this a shot:
SELECT
E.Id,
E.Name,
STUFF(
(
SELECT ',' + D.Department AS [text()]
FROM (
SELECT A.[id],
Split.a.value('.', 'VARCHAR(100)') AS DeptId
FROM
(SELECT [id],
CAST ('<M>' + REPLACE(Depts, ',', '</M><M>') + '</M>' AS XML) AS String
FROM Employee
) AS A
CROSS APPLY String.nodes ('/M') AS Split(a)) A
JOIN Departments D ON A.DeptId = D.Id
WHERE E.Id = A.Id
FOR XML PATH('')
), 1, 1, '') AS Departments
FROM Employee E
And here is the SQL Fiddle.
Good luck.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With