Query For Comma-separated Ids To Comma-separated Values

May 30, 2023 Post a Comment

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

Solution 1:

DECLARE@DepartmentsTABLE 
(
  ID INTPRIMARY KEY, 
  Dept VARCHAR(32) NOTNULLUNIQUE
);

DECLARE@EmployeesTABLE
(
  ID INTPRIMARY KEY,
  Name NVARCHAR(64) NOTNULL,
  Depts VARCHAR(255) NOTNULL
);

INSERT@DepartmentsVALUES 
  (1,'HR'),  (2,'Accts'),  (3,'IT');

INSERT@EmployeesVALUES
  (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@DepartmentsAS d
    INNERJOIN@EmployeesAS ei
    ON','+ ei.Depts +','LIKE'%,'+CONVERT(VARCHAR(12), d.id) +',%'WHERE ei.ID = e.ID
    ORDERBY Dept
    FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
FROM@EmployeesAS e
ORDERBY 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:

ORDERBY Dept

To:

ORDERBY 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 -- thisis the only one affected as it turns out

However, in reality, you should normalize your database. This is an absolute nightmare.

Solution 2:

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) ASStringFROM  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.

Solution 3:

You can also use a recursive CTE to split the data and then use FOR XML PATH to concatenate the rows into a single row:

;with cte (id, name, deptid, depts) as
(
  select id, name,
    cast(left(depts, charindex(',',depts+',')-1) asvarchar(50)) deptid,
         stuff(depts, 1, charindex(',',depts+','), '') depts
  from employee
  unionallselect id, name,
    cast(left(depts, charindex(',',depts+',')-1) asvarchar(50)) deptid,
    stuff(depts, 1, charindex(',',depts+','), '') depts
  from cte
  where depts >''
) 
select e.id, e.name,
  stuff((
         selectdistinct', '+ d.dept
         from cte c
         innerjoin departments d
            on c.deptid = d.id
         where e.id = c.id
         for XML path('')),1,1,'') Depts
from employee e

See SQL Fiddle with Demo

Result:

| ID |     NAME |          DEPTS |
----------------------------------
|  1 |    Kevin |      Accts, HR |
|  2 | Michelle |             HR |
|  3 |     Troy |         HR, IT |
|  4 |   Rheesa |  Accts, HR, IT |

Solution 4:

Also you can use option with dynamic management function sys.dm_fts_parser Before script execution you need check full-text component is installed:

SELECT FULLTEXTSERVICEPROPERTY('IsFulltextInstalled')

0 = Full-text is not installed. 1 = Full-text is installed. NULL = Invalid input, or error.

If 0 = Full-text is not installed then this post is necessary to you How to install fulltext on sql server 2008?

SELECT b.ID, b.Name, STUFF((
SELECT','+ d.Dept
FROM Employees e 
  JOIN Departments d ON d.ID IN(
                                SELECT display_term 
                                FROM sys.dm_fts_parser('"'+ e.Depts +'"', 1033, NULL, 0) 
                                WHERE display_term NOTLIKE'nn%'
                                )
WHERE b.ID = e.ID
ORDERBY d.Dept
FOR XML PATH('')), 1, 1, '') AS Depts
FROM Employees b

SELECT e.ID, e.Name, 
 (
  STUFF((
  SELECT','+ Dept
  FROM sys.dm_fts_parser('"'+ e.Depts +'"', 1033, NULL, 0) p JOIN Departments d ON p.display_term = d.ID
  WHERE display_term NOTLIKE'nn%'FOR XML PATH('')), 1, 1, '')
  ) AS Depts
 FROM Employees e

Solution 5:

Write a function for splitting comma separated values. I wrote dbo.split

select * from dbo.split('1,2,3',',') Will return as - Data 1 2 3

SELECT tact.ActivityID,CONVERT(NVARCHAR(20),tact.createddate,103) AS CallDate,
ActivityOriginatedByPartyID , (ISNULL(p.firstname,'')+' '+ISNULL(p.lastname,'')) AS PartnerName,
u.UserName AS PSTName, ccv.codevalue AS CallType
**, ccv1.codevalue AS CallContext**
,tact.ActivityNote AS CallTrackerNote,
(CONVERT(VARCHAR(20),tact.ActivityTimeSpend) +' Min') AS CallDuration
FROM txn_activity tact (NOLOCK)
INNERJOIN TXN_Party p (NOLOCK) ON p.PartyID = tact.ActivityOriginatedByPartyID 
INNERJOIN TXN_User u (NOLOCK) ON u.userid = tact.createdby
INNERJOIN CFG_CodeValue ccv (NOLOCK) ON ccv.codevalueid = tact.ActivityTypeID
--INNER JOIN CFG_CodeValue ccv1 (NOLOCK) ON ccv1.codevalueid = tact.ActivityContext**CROSS APPLY  
    dbo.split(tact.ActivityContext,',') split
innerjoin 
    dbo.CFG_CodeValue ccv1 (NOLOCK) ON ccv1.codevalueid = split.data**

Lab Sql