Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Query for comma-separated ids to comma-separated values

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
like image 840
Sanjay Kattimani Avatar asked Jan 30 '13 19:01

Sanjay Kattimani


People also ask

How do you write query for comma separated values in SQL?

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.

How do you convert lists into comma separated values?

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.


2 Answers

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.

like image 190
Aaron Bertrand Avatar answered Nov 05 '22 16:11

Aaron Bertrand


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.

like image 23
sgeddes Avatar answered Nov 05 '22 16:11

sgeddes