Mintyman (mi******@ntlworld.com) writes:

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

I the longest list would be 40 characters, this means that there are not

that many materials per company. Since you said no limit, I was afraid

that there was a risk that you could exceed the limit of 4000 for an

nvarchar. In that case, you would have been in real dire straits. Unless

you had been on SQL 2005 where this would have been much simpler.

Here is an example of a query that runs in Northwind. First run:

select max(cnt) from

(select OrderID, cnt = COUNT(*)

from [Order Details]

group by OrderID) s

(but translated to your database). This gives the longest list in number

of elements. In case of Northwind the returned number is 25 which is a tad

many. With a maximum of 40 characters per list, a maximum of seven seems

reasonable. Using that number, here is a query for Northwind that

returns a comma-separated lists per order:

SELECT OrderID,

MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END) +

coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + P.ProductName END), '') +

coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + P.ProductName END), '') +

coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + P.ProductName END), '') +

coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + P.ProductName END), '') +

coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + P.ProductName END), '') +

coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + P.ProductName END), '')

FROM (SELECT a.OrderID, a.ProductID,

rowno = (SELECT COUNT(*)

FROM [Order Details] b

WHERE b.OrderID = a.OrderID

AND b.ProductID <= a.ProductID)

FROM [Order Details] a) AS OD

JOIN Products P ON P.ProductID = OD.ProductID

GROUP BY OD.OrderID

ORDER BY OD.OrderID

If your maximum number is 8, you will need to add one more line.

Caveat: the performance of this is not fantastic. The big culprit is

the SELECT that computes the row number. If you have millions and millions

of rows in that table, you may bave to find a different way to compute

the row number. One way would to be bounce the data over a temp table

with an IDENTITY column. But before you go that route, try a query like

the one above.

If you need to compose many of these queries, I would suggest that you

look into the third-party tool RAC,

http://www.rac4sql.net/ which can

help you to generate such queries.

--

Erland Sommarskog, SQL Server MVP,

es****@sommarskog.se
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinf...ons/books.mspx