How to Pull a List of Id's from a SQL Table as a Comma-Separated Values String

declare @string nvarchar(255)

select @string = coalesce(@string + ', ', '') + cast(prodid as nvarchar(5))
from products

 create table test (id int, studentIDs  varchar(50));
 insert into test values(1,'1,2,3');
 create table student (id int, name  varchar(50));
 insert into student values(1,'A');
 insert into student values(2,'B');

 select * from student where find_in_set(id,(select studentids from test))

id	name
1	A
2	B

get All records which has all IDs passed in comma separated string in sql server

If you want tutors with all subjects, you can use group by and having:

Select tutorid
from TutorSubject
where SubjectId in (92, 106, 1, 2, 91)
group by tutorid
having count(*) = 5;  -- 5 is the length of the string

If you want to pass this in:

with s as (
      select convert(int, value) as subjectid
      from string_split(@s)
     )
select tutorid
from tutorsubject ts join
     s
     on ts.subjectid = s.subjectid
group by tutorid
having count(*) = (select count(*) from s);

How do I select records from a table and order it by ID in a comma separated string?

You can try this script-

DEMO HERE

--Consider this first parameter is you retrieved 
--order string from another table
DECLARE @order_ VARCHAR(MAX) = '3,5,4,1,2'

--Added Comma at the start and end of the order string
--So that id 1 and 11 do not conflict
DECLARE @order_new VARCHAR(MAX) = ','+@order_+','

SELECT *
FROM your_table
ORDER BY CHARINDEX(
    ','+CAST(id AS VARCHAR)+',
    ',@order_new,
    0
) 

You can also ignore the second step of adding additional Comma to the order string and do it directly in the script as below which will return the same output-

SELECT *
FROM your_table
ORDER BY CHARINDEX(
    ','+CAST(id AS VARCHAR)+',',
    ','+@order_+',',
    0
)

Here is the final output-

filter ids by comma separated nvchar ids

You may use this. You need to look from other side in this problem. I found it very interesting.

Actually you have a list of id's as base, and you want to exclude id from the string which are not in some table. So first we need to find the list of id's, after that we'll exclude them from the id's of table to get our desired result. At the end you may use stuff or string_agg to convert your final result into , separated string.

select Value from (
select value from string_split('1,2,3',',')) as t 
where t.value not in (select id from demotable)

You may check this link for working fiddle.FIDDLE.

Return comma separated list of names from comma separate list of IDs

Do 2 separate queries, one of the Personnel tables and one of Techniques.

Process the Techniques query result into an associative array, where the index is the ID, and the value is the Name.

When outputting the Personnel.Technique_ID field, process the comma separated value as a list. Loop through each item in the list, and output the value of the Techniques associative array.

get names from comma separated ids in SQL

To do that join a table with integers, so that every employee row occurs as often as there are department IDs in its string but at least one time. For the rows in the join result the numbers i go from 1 to n, where n is the number of IDs in the string for that employee (if there are any department IDs for the employee). Then you can use REGEXP_SUBSTR() to get the _i_th number from the string. Use that to left join the departments, to get the department name. Then use an aggregation using LISTAGG() to get a single row for each employee again.

SELECT E.EMPID,
       E.NAME,
       E.DEPTID,
       LISTAGG(D.DEPTNAME, ',') WITHIN GROUP (ORDER BY I.I) DEPTNAME
       FROM EMPLOYEE E
            LEFT JOIN (SELECT ROW_NUMBER() OVER (ORDER BY DEPTID) I
                              FROM DEPARTMENT) I
                      ON I.I <= REGEXP_COUNT(E.DEPTID, ',') + 1
            LEFT JOIN DEPARTMENT D
                      ON D.DEPTID = TO_NUMBER(REPLACE(REGEXP_SUBSTR(',' || E.DEPTID, ',([[:digit:]]+)', 1, I.I), ',', ''))
       GROUP BY E.EMPID,
                E.NAME,
                E.DEPTID;

db<>fiddle

---

Related Topics