Search code examples
sqlsql-server-2008ms-access-2007pivotcrosstab

Cross Tab query in SQL Server 2008

I have a SQL table with following fields

ID, Rly, Shed, Loco_no, shed, Date_failure, Equipt

I am using a cross tab query in access using the following script in MS Access 2007

TRANSFORM Count(Sheet1.[sno]) AS CountOfsno
SELECT Sheet1.[Equipt], Count(Sheet1.[sno]) AS [Total Of sno]
FROM Sheet1
GROUP BY Sheet1.[Equipt]
PIVOT Sheet1.[Shed];

How can this query be converted to a SQL Server stored procedure for use on an aspx page?

please help

Solution

  • In SQL Server you will want to look at the PIVOT function. Considering you did not provide many details on your table or sample data, based on your attempt in the comments something like this might point you in the right direction.

    SELECT *
FROM
(
    SELECT Equipt, Shed
    FROM PunctualityMain
) x
PIVOT
(
    COUNT(Equipt)
    FOR Shed IN ([BSL], [AQ])
) p

    If you post some more details, then we could provide additional help.