Rows Into a Single Column With T-SQL's FOR XML PATH

Concatenating Rows Into a Single Column With T-SQL's FOR XML PATH

Turn multiple rows per person into one readable row with a comma-separated list — using FOR XML PATH to concatenate values, filtered down to only the people who match a given set of criteria. No CURSOR. No loop. One query.

Published · Tags: T-SQL, Microsoft SQL, FOR XML PATH, String Concatenation, SQL Server, Subqueries

Someone asks for a list of people who speak Spanish or French, along with every other language each of those people speaks. The underlying data is one row per person per language — exactly the shape you want for storage, and exactly the shape no one wants to read in a report. A person who speaks three languages shouldn't show up as three separate rows with the same name repeated. They should show up once, with their languages listed together in a single column.

This is a classic row-to-column concatenation problem, and FOR XML PATH is one of the cleanest ways to solve it in T-SQL — no cursor, no loop, no string-building variable carried across iterations.

The Two-Part Problem

This query actually answers two questions at once, and it helps to separate them mentally before looking at the SQL:

• Which people qualify? Only people who speak at least one of the target languages (Spanish or French) should appear in the results at all.
• What should their row look like? Once a person qualifies, show every language they speak — not just the ones that qualified them — concatenated into one column.

The query handles the first question with an inner join to a filtered subquery, and the second with a correlated subquery that uses FOR XML PATH to do the concatenation.

The Query

create table #peoplelanguage (people varchar(15), [language] varchar(15))
insert into #peoplelanguage values ('Me','English')
insert into #peoplelanguage values ('Me','German')
insert into #peoplelanguage values ('You','French')
insert into #peoplelanguage values ('You','Spanish')
insert into #peoplelanguage values ('You','Italian')
insert into #peoplelanguage values ('Her','English')
insert into #peoplelanguage values ('Her','French')
insert into #peoplelanguage values ('Her','Spanish')

declare @language varchar(15)
set @language = 'Spanish,French'

select distinct peop.people as people
, (select langxpath.[language] + '; '
   from #peoplelanguage as langxpath (nolock)
   where peop.people = langxpath.people
   order by langxpath.[language]
   for xml path('')) as [language]
from #peoplelanguage as peop (nolock)
inner join (
  select distinct people
  from #peoplelanguage (nolock)
  where charindex([language],@language)>0
) as lang
on peop.people = lang.people

Piece	Purpose
#peoplelanguage	Sample data — one row per person per language spoken, the natural normalized shape for this kind of data.
Inner subquery (lang)	Finds the distinct people who speak at least one language matching @language, using charindex() to check membership in the comma-separated list. This decides who qualifies.
Correlated subquery with for xml path('')	For each qualifying person, pulls every language they speak — not just the qualifying ones — concatenated into a single semicolon-separated string. This decides what their row looks like.
order by langxpath.[language]	Keeps the concatenated language list alphabetized within each person's row, for consistent, readable output.
Outer select distinct ... inner join	Combines the two: one row per qualifying person, with the full concatenated language list attached.

How FOR XML PATH('') Does the Concatenation

The trick is in the correlated subquery. Rather than returning a normal result set, for xml path('') tells SQL Server to serialize the subquery's output as XML — and with an empty path ('') and a single concatenated text column, that "XML" collapses down to nothing more than the column values strung together with no element tags wrapping them. The subquery is correlated to the outer query by peop.people = langxpath.people, so it re-runs once per person, gathering just that person's languages into one string.

Why not just use STRING_AGG? Newer versions of SQL Server support STRING_AGG(), which does the same job with cleaner syntax and no XML involved. This FOR XML PATH pattern predates that function and remains useful for older SQL Server versions, or any environment where STRING_AGG isn't available — and it's still common enough in production code that it's worth knowing how to read.

Changing OR to AND

As written, the query finds anyone who speaks Spanish or French. If the requirement changes to "people who speak both Spanish and French," the charindex() trick on a single comma-separated variable won't get you there — that pattern is inherently an OR test. Instead, split the qualifying subquery into two separate subqueries, one per required language, and join through both:

Requirement	Approach
Speaks Spanish or French	Single subquery filtering on charindex([language],@language)>0, as shown above.
Speaks Spanish and French	Two separate subqueries — one filtered to Spanish speakers, one filtered to French speakers — joined together so only people present in both qualify.

What You Can Do With It

• Use this pattern any time you need a readable, one-row-per-entity report from naturally normalized, one-row-per-attribute data
• Swap the filtering subquery's logic to require ANY, ALL, or a specific combination of qualifying values without changing the concatenation logic
• Adjust the separator inside the correlated subquery — semicolon, comma, pipe — to match whatever downstream system or report format consumes the result
• Reach for STRING_AGG() instead if you're on SQL Server 2017 or later and don't need to support older versions
• Keep the order by inside the correlated subquery whenever consistent, alphabetized output matters for the report

---

A small pattern, but one that comes up constantly once you start looking for it — anywhere a report needs "one row per person, with a list of their related values" instead of the normalized one-row-per-relationship shape the data actually lives in.