|
|
Posted by Mintyman on 01/05/07 09:19
Hi Erland,
Thanks for the script. The difference between the Northwind database and
mine is that all the data I want to get access to is in one table (unlike
Northwind where it is spread over [order details[ and [products]. I tried
modifying the script but it doesn't work:
SELECT to_company,
MAX(CASE OD.rowno WHEN 1 THEN Material_Name END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + Material_Name END), '')
FROM Material__Bridge AS OD
GROUP BY OD.to_company
ORDER BY OD.to_company
It says there is an invalid column name 'rowno' - I guess this is right
because there is no column with that name in my database! However, when I
check in Northwind, there isn't one called that there either!
Any ideas?
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns98AED9C3878CYazorman@127.0.0.1...
> 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]
|