Posted by Erland Sommarskog on 12/22/06 14:19

Mintyman (mintyman@ntlworld.com) writes:
> I'm working on a system migration and I need to combine data from multiple
> rows (with the same ID) into one comma separated string. This is how the
> data is at the moment:
>
> Company_ID Material
> 0x00C00000000053B86 Lead
> 0x00C00000000053B86 Sulphur
> 0x00C00000000053B86 Concrete
>
> I need it in the following format:
> Company_ID Material
> 0x00C00000000053B86 Lead, Sulphur, Concrete
>
> There is no definite number of materials per Company.
>
> I have read the part of
> http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about
> 'The Iterative Method' but my knowledge of SQL is very limited and I
> don't know how to use this code to get what I need.

And that article covers the opposite process - unpacking the list.

Composing the list is less funny, because it produces a result which
violates basic principles in a relational database: no repeating groups.
That is not to say that it's a stupid thing to ask for; it's not strange
to ask for this format in reporting. I get a little nervous when you
say that you are working with system migraton, because that means that
someone will have to handle the comma-separated list on the other side,
and is not funny at all. But I assume that you don't have control over
that.

Anyway, to give a good answer to the question, I would need to know a
few more things:
o Which version of SQL Server?
o What is a reasonable upper limit of the comma-separated string? You
could determine the current max value with this query:

SELECT MAX(listlen), AVG(listlen)
FROM (SELECT SUM(len(Material) + 2)
FROM tbl
GROUP BY Company_ID) as a

o What is the datatype of Material? That is, is varchar or nvarchar?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]