|
|
Posted by Mintyman on 01/04/07 08:52
Hi Erland,
I hope it's not to late to get help on this one!
Here are the answers you are looking for:
1) I'm using SQL 2000
2) 40
3) nvarchar
To clarify the field names, it is 'material_name' instead of 'material' and
is 'to_company' instead of 'company_id'
Thanks!
Mintyman
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns98A19BE4CF73BYazorman@127.0.0.1...
> 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]
|