Comma Separated List

Comma separated results in SQL

Update (As suggested by @Aaron in the comment)

STRING_AGG is the preferred way of doing this in the modern versions of SQL Server (2017 or later). It also supports easy ordering.

SELECT
    STUDENTNUMBER
    , STRING_AGG(INSTITUTIONNAME, ', ') AS StringAggList
    , STRING_AGG(INSTITUTIONNAME, ', ') WITHIN GROUP (ORDER BY INSTITUTIONNAME DESC) AS StringAggListDesc
FROM Education E
GROUP BY E.STUDENTNUMBER;

Original Answer:

Use FOR XML PATH('') - which is converting the entries to a comma separated string and STUFF() -which is to trim the first comma- as follows Which gives you the same comma separated result

SELECT
    STUFF((SELECT ',' + INSTITUTIONNAME
              FROM EDUCATION EE
              WHERE  EE.STUDENTNUMBER = E.STUDENTNUMBER
              ORDER BY sortOrder
              FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)')
        , 1, LEN(','), '') AS XmlPathList
FROM EDUCATION E
GROUP BY E.STUDENTNUMBER

Here is the FIDDLE showing results for both STRING_AGG and FOR XML PATH('').

Comma separated list into matched columns pairings

alternative by @Matt:King:

=ARRAYFORMULA(QUERY(VLOOKUP(SEQUENCE(COUNTA(A2:A)*
 COLUMNS(SPLIT(B2:B, ",")), 1, 0)/ COLUMNS(SPLIT(B2:B, ","))+2,
 {ROW(A:A), A:A, TRIM(SPLIT(B:B, ","))}, MOD(SEQUENCE(COUNTA(A2:A)*
 COLUMNS(SPLIT(B2:B, ",")), 1, 0), COLUMNS(SPLIT(B2:B, ",")))*{0, 1}+{2, 3}),
 "where Col2 is not NULL"))

How do I create a comma-separated list using a SQL query?

There is no way to do it in a DB-agnostic way.
So you need to get the whole data-set like this:

select 
  r.name as ResName, 
  a.name as AppName
from 
  Resouces as r, 
  Applications as a, 
  ApplicationsResources as ar
where
  ar.app_id = a.id 
  and ar.resource_id = r.id

And then concat the AppName programmatically while grouping by ResName.

How can I separate a comma separated list by character count (1000) but avoid splitting the words between commas?

Looking at your google doc, I think this is an XY problem. (https://en.wikipedia.org/wiki/XY_problem)

Rather than helping you split a long string as per your question, it's far easier to help you join the list of words into a series of strings which are each less than 1000 characters. It works by giving each word a group number, ensuring the groups are less than 1000 characters when joined. Then join together the words in each group.

1. Insert 3 helpers columns after column A
2. B3: =LEN(A3)+1 fill down (length of words plus comma)
3. C2: 0 (cumulative length start)
4. C3: =IF(C2+B3<1000,C2+B3,B3) fill down (cumulative length, resets at 1000)
5. D3: =IF(OR(C3<C2,C2=0),D1+1,D1) fill down (group counter)
6. E1, E2, E3...: 1, 2, 3 fill across
7. E2: =TEXTJOIN(",",TRUE,OFFSET($A$3,MATCH(E$1,$D$3:$D$1002,0)-1,0,COUNTIF($D$3:$D$1002,E$1),1)) fill across
8. E3: =LEN(E2) fill across to check

In the final formula, you can replace the references to E$1 with COLUMN()-4 if you want.

---

Related Topics