Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006

    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

  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    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
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts