Unanswered: Changing an access transform SQL query into an SQL Server Pivot
I'm using SQL Server 2008
As background info, I'm asking this because I'm playing with moving from using an Access interface and server to using an ASP.NET interface and SQL SERVER. Quite a learning curve...
Below is a working SQL statement however in access I used FIRST instead of COUNT as an aggregate function. It would return the first Products.Name value. Since Products.Name is a text field, within SQL Server, I can only use COUNT which is useless.
I've done a little research and I think that maybe I can do this with CASE statements and I will keep trying to crack this myself but can anyone suggest a way to do what I want?
SELECT SampIDBarcode, [Level]
FROM (SELECT Samples.SampIDBarcode, Samples.[Level], Products.ProductID, Products.Name AS PivotFld
FROM Samples INNER JOIN
(Products INNER JOIN
ProductLists ON Products.ProductID = ProductLists.ProductID) ON Samples.SampIDBarcode = ProductLists.SampleID
WHERE Samples.TrialID = '2007-1-1' AND SampleTypeID = 1) AS p PIVOT (count(ProductID) FOR PivotFld IN ([P], [M], [U], [B])) AS pvt
I would use VARCHAR(MAX) or NVARCHAR(MAX) and not text.
There is not, however, no equivalent of FIRST(). That aggregate function is rather naughty TBH - it is not standard SQL nor compatible with the relational model.
I would also avoid using pivot - let your front end handle the pivoting.
(I haven't used this, but Jeff is normally pretty spot on with his articles): ASP / ADO Pivot Function & Example