Posted by Erland Sommarskog on 01/04/07 20:24

Mintyman (mintyman@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, 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]